In our earlier article we explained there were several ways to convert Microsoft Word data such as address listings to Microsoft Excel. This next method starts by converting your text to a table. You’ll probably find this method easier.
If you missed our earlier article, the problem we’re trying to solve is to get a list of data items that expand down into Excel. The example people mentioned to me were address directories. For example, how do you take an address list of 100 members and get them into Excel? Each entry was three lines such as the following:
34 Parson Dr.
Omaha, NE 68001
Prepping Your Word Document
Whenever we convert listings, we copy the data we need and paste it to a new document. The reason is twofold. First, if anything happens to our data, we still have the original. Secondly, you don’t want extraneous text such as a description of the list or instructions on updating your entry.
Steps to convert using Tables,
- Copy and paste text you need to convert to a new document
- Highlight the text to convert
- From Word’s Table menu, select Convert | Text to Table
- In the number of columns: field, select the number of lines that make one record. If you have a blank line between records, add 1.
- Keep the default entry for Separate text at Paragraphs.
- Click OK.
- To get your info into Excel, highlight the table columns you need and press Ctrl+C
- Open a new worksheet in Excel.
- In cell A1 press Ctrl+V and paste you data.
The Convert Text to Table dialog box appears.
Word will convert your text and present a table such as the one below.
Tweaking the Excel Data
Although your data copied, you probably want to convert the last column into 3 columns. These would represent the city, state, and zip code.
Sometimes all your cities are one word. Other times, you get cities with two or more words. In this case, you want to first break this column apart after the comma that separates state from zip code.
- Click the column you wish to parse such as C
- From the Data menu, select Text to Columns. This will start the Convert Text to Columns Wizard.
- On Step 2 of the wizard, select the Comma checkbox for your Delimiter. Excel will refresh and you can see how the column will break in the Data Preview section.
- Click Finish.
- The last step is to insert a row at the top and place in your column headings.
Once you’ve split these columns you may notice your new Column D starts with an extra space. This is OK as we’ll do one more Text to Columns conversion, but use a Space as a delimiter instead of the comma. This will result in three columns. The first column will be blank as it represents that leading space before the city name. You can delete this blank column.
Between these two methods, you should be able to convert most text listings in Word. There may be some adaptations you need to make. For example, you may have entries that have an extra line showing an Apartment number or Suite. Depending on the quantity of these irregular entries, you may want to copy them off a separate document and handle it after your main conversion. Or, you may want to add the apartment info at the end of line 2. It’s your list so you get to define the rules. We just don’t want you to do a copy and paste of each line into Excel.
You Might Also Like These Excel Tutorials
- How to Extract Text in Excel Cell
- Quick Guide to XLOOKUP in Excel
- How to Transpose Columns and Rows in Excel
Disclaimer: Images from Amazon Product Advertising API. This site may receive an affiliate commission on these products. Updated: 2020-07-10