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!

  • Is there just a single column of cells? If so, perhaps you could use Text-to-columns, splitting on the linefeed character – Ron Rosenfeld Nov 17 '15 at 22:05

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.