How to Convert Word List to Excel

Have you ever received data in an inefficient format? Maybe it was a contact list that looked more like mailing labels. In this tutorial, I’ll show how to convert a Microsoft Word list so you can use it with Microsoft Excel.

The Conversion Scenario

If you missed our earlier article, we’re trying to solve the problem of getting an address list into Microsoft Excel. For example, how do you get an address list of 100 contacts into Excel? Instead of having one contact record per line where we could split first and last names, each entry was three lines, such as the following:

James Hudson
34 Parson Dr.
Omaha, NE 68001

Prepping Your Word Data

Whenever we convert listings, I copy the data I need and paste it into a new document. The reason is twofold. First, I still have the original if anything happens to the data. Secondly, you don’t want extraneous text, such as a description of the list or instructions on updating your entry. So I tend to cut that information out.

Create a Word Table

We opted for a Word table because the format is similar to an Excel spreadsheet. The Word table we’ll create has one row for each contact. I should mention that although the word “table” is in both Microsoft programs, we’re not copying the data to an Excel Table. Excel tables are a special feature that provides additional benefits.

  1. Copy and paste the contacts or text you need to convert to a new document.
  2. Highlight the text to convert.
  3. From Word’s Table menu, select Convert Text to Table
Convert Text to Table option.
Highlighted addresses to convert
  1. 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.
Convert Text to Table dialog and options.
Defining the number of columns
  1. Keep the default entry for Separate text at Paragraphs.
  2. Click OK.
Converted addresses in Word table.
Addresses in table format
  1. If you don’t want the borders to show, you can go to Table Design and choose No borders.

Copying Word Table to Excel

  1. To get your data into Excel, highlight the table and press Ctrl + C
  2. Open a new worksheet in Excel.
  3. In cell A1, press Ctrl + V and paste your data.
  4. Add your column headers.
Excel table with pasted entries.
Pasted address entries from Word

Tweaking the Excel Addresses

In this example, our contact records were pretty uniform. Each record had 3 lines. However, you may want to adjust the results to suit your needs. Excel offers several functions to parse text.

For example, you might want to split Column A into two fields representing First Name and Last Name. The easiest way is to use Excel to convert text to columns. Then, you could do a similar extraction process to separate the city.

In addition, you might want to split Column B into Street Number and Street. This example is different because you have multiple spaces. In this case, you could parse the data using Excel substrings.

As you see, this conversion process is a bit more complex than a straight mail merge using Excel and Word, but sometimes you need to take an alternate path when data isn’t in the optimal format.