I was sent 5 pages of a Microsoft Word table for my sister's wedding guest's. Each individual cell contains contains all the info for a card to send in the mail: Mr. and Mrs. John Doe, 1111 Address Street, City, State, Zip. I was asked to transfer this information to a Microsoft Excel spreadsheet with the columns as follows: Title (Mr. and Mrs. etc), Name, Address, City, Zip. Each row is supposed to be a different entry. As the info is all located in one cell, I cannot figure out the best way to import it without manually doing a copy/paste. Does anyone know of a time efficient method of importing this data from the Microsoft Word tables to a Microsoft Excel 2013 spreadsheet and having it put the info in the correct places? Thank you for any and all tips, suggestions and/or solutions!
1 Answer
This really depends a LOT on how the data is structured inside the individual cells. Options include
Text to columns: could work if each row is separated by a specific character. You may need to replace the line feed character in Word with another character like a pipe symbol, then use the pipe symbol as the delimiter.
Flash fill: With the data copied into Excel, type the first two values into the first two cells. Flash fill should kick in and suggest how it would parse the data. Very powerful.
Power Query, which is a free add-in from Microsoft for 2010 and up. This might be a bit of overkill
VBA: write a VBA macro to parse the data, but the structure would have to have some logic, and it will probably take longer to write the VBA than to do the first two above.