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.Read more
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 take an address list of 100 contacts and get them 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:
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, if anything happens to the data, I still have the original. 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
The reason we opted for a Word table is that 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.
- Copy and paste the contacts or 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.
- If you don’t want the borders to show, you can go to Table Design and choose No borders.
Copying Word Table to Excel
- To get your data into Excel, highlight the table and press Ctrl + C
- Open a new worksheet in Excel.
- In cell A1, press Ctrl + V and paste your data.
- Add your column headers.
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.