One item I’ve learned from using software is that there is usually more than one way to solve a problem. Recently, two people approached me with a similar problem. They were trying to get a Microsoft Word mailing list into Microsoft Excel. One tried macros, and the other resorted to cut and paste. I thought a simpler solution in each case involved Word’s Search and Replace feature. In this tutorial, I’ll show how to convert Word to Excel.
The Starting Address Format
For this tutorial, I’m using Microsoft 365. The steps are very similar in older versions of Microsoft Word and Microsoft Excel. The main difference is the user interface.
Each user started with a list of names and addresses in Microsoft Word. I suspect they were some address directory or contact list. For various reasons, they needed to get the data into Microsoft Excel. However, they wanted one row for each contact record. Also, they wanted to split the data into distinct columns. Thus, the address record looked like the records below.
James Madison
124 Main St
Anytown, NY 12345
Paula Harris
356 Longtree View
Harper, MA 01073
Before starting, review your address list and look for common denominators and possible exceptions. In these cases, the records were uniform, with each contact consisting of three lines with a blank line in between. You’ll also want to fix object replacement characters like or �.
Another approach you could take is to convert the list to a Word table and then copy it to Excel. It really depends on how comfortable you are with Word and Excel. As I said, there are multiple solutions.
Creating the Record Delimiter
The first step in this process is to add a record delimiter. This is the item Excel will look for to separate each contact.
- Open your Word document.
- Turn on Paragraph marks ¶ using Ctrl + Shift + * or click the Paragraph button on the Home menu.
- Notice how a paragraph mark exists at the end of each record. We’ll substitute a unique character as a record delimiter.
I like to use the tilde ~ sign, but you can use any uncommon character. Be careful not to use a character that appears on your list. For example, if your list also included email addresses, you wouldn’t want to use the @ sign as your field delimiter.
- Go to the top of your document. Ctrl + Home.
- From the Editing group, select Replace.
- Click the Replace tab.
- Click the More >> button at the bottom. Your dialog will now show more Search Options.
- Click the Special button.
- Select Paragraph Mark from the pop-up menu.
- Enter the symbol you wish to use for your record delimiter, such as a tilde in the Replace with: textbox.
Your Find and Replace dialog should look like the one below.
- Click Replace All.
- Click Close.
Microsoft Word will show a count of how many replacements it made. So don’t worry that your formatting looks off and various lines look combined.
Defining the Fields
The next part is to define our fields which will be placed in Excel columns. Each record had 3 lines representing: Name, Address, and City, ST, and Zip. In this example, we’re going to use a comma to separate these fields. We can parse the Names and States in Excel later.
- Go to the top of your document.
- From the Edit menu, select Replace.
- Your Find and Replace dialog will retain your previous values.
- In the Find what: text box, clear your previous entry. Click Special and select Manual line break from the popup menu. This will add a ^l.
- Clear out the tilde and enter a , in the Replace with: text box.
- Click Replace All.
- Click Close.
Breaking Apart the Records
Your document probably looks worse, but don’t worry about it. Part of this may be word wrap, and part of it is our formatting.
The next steps will put it into perspective.
- Go to the top of your document
- From the Edit menu, select Replace.
- Replace the Manual Line break code in the Find what: text box with a tilde.
- Clear the comma in the Replace with: textbox and enter ^l for a manual line break.
- Click Replace All.
- Click Close.
If you have extra commas or paragraph marks on the last line, you can delete them. If you’re really fastidious and don’t like the space before the State, you can do another “search and replace” or fix it in Excel.
Saving the File
Your document should now have 1 record per line with the fields separated by a comma and ending with a manual line break symbol ↵ . There will not be a comma between state and zip code.
- From the File menu, select Save As.
- In the Save As dialog, enter your file name.
- In the Save as type: drop-down menu, select Plain Text.
- Word may display a File Conversion dialog with a warning that all formatting will be lost. Don’t worry, and click OK to accept the default values.
Import the File into Microsoft Excel
The last part is to import our Microsoft Word text file into Excel.
- Open Excel
- From the File menu, select Open.
- Click Browse.
- In the Open dialog, change the Files of Type: entry to Text Files (*.prn;*.txt;*.csv)
- Point to your .txt file.
- Click Open
- The Text Import Wizard should start. Keep the default values and click Next >.
- In Step 2, change your Delimiter from Tab to Comma. The screen should adjust to show the values in columns.
- In Step 3, you can change the data format for each column or click Finish to accept the General format.
Final Tweaks and Thoughts
Chances are you will want to do some minor tweaking. For example, you probably want to add Excel column labels. If you have US addresses, you may want to split the last column with the State and Zipcode into separate columns. This can be done with the Text to Columns feature. However, if you have zip codes with leading zeros, make sure you don’t drop lose them.
You may also want to split the name column into first and last names. In our example, this is easy as 1 space separates the first and last name. The same is true for the state and zip code. Sometimes, you may have more complicated needs, like putting the street number in its own field. You can use an Excel formula to pull out substrings in those cases.
While these steps may not map precisely for your list, they should provide the basic steps for creating the records in Microsoft Word. Your list may be slightly different or include more items, such as email addresses. Either way, you could use a similar framework to create a document that Microsoft Excel can interpret.
Now that you’ve learned how to convert Word to Excel take some time to reflect if Word was the best tool for the job. I appreciate both Word and Excel, but each has its strengths and weaknesses.