Blog

How to Extract Data from Microsoft Word

The programs in Microsoft Office may each stand on their own, but there are some tasks that they can share to make your life easier.

Let’s say you have a list, from which you want to extract the e-mail addresses (or some other data). That could get very tedious if you tried it manually. What follows are a couple of scenarios in which your ultimate goal is to use Outlook or another program or online service to send e-mails to multiple people.

The first list you get is in Word or a text file and has all the information separated by commas:

Arthur G. Pym, 532 N. 7th St., Philadelphia, PA, 19735, Pym@example.com
Rebecca de Winter, 123 Manderley Ave., Cornwall Twp., NJ, 08765, RdW@example.com
Rowan Bean, 52 Festive Rd., Putney, PA, 19874, Mister@example.com
Tyler Durden, 537 Paper St., Bradford, DE, 18963, Soap@invalid.com
Bertram Wooster, 15 Berkeley St., Mayfair, NJ, 08543, Bertie@sample.com

The easiest way to snag the e-mail addresses (or any set of similar data) is by converting the text to a table format. First, highlight all the text; then click Insert, then the down arrow under Table, then choose Convert Text to Table. A dialog box will open; in it you can control how the table is laid out. Initially, Word probably chooses the wrong number of columns; ignore that. It assumes you want the cells to break where there is a space, but that would create a mess, since there are varying numbers of spaces in each line. Go to the bottom of the dialog and choose Commas in the last section. Done.

Arthur G. Pym 532 N. 7th St. Philadelphia PA 19735 Pym@example.com
Rebecca de Winter 123 Manderley Ave. Cornwall Twp. NJ 08765 RdW@example.com
Rowan Bean 52 Festive Rd. Putney PA 19874 Mister@example.com
Tyler Durden 537 Paper St. Bradford DE 18963 Soap@invalid.com
Bertram Wooster 15 Berkeley St. Mayfair NJ 08543 Bertie@sample.com

 

Well, you’re not quite done yet. You can’t just paste the column of addresses into the To: field of an e‑mail. You have to separate the addresses properly. In Outlook, you’ll use a semicolon and a space. In Gmail’s interface, use a comma; if you use something else, check the Help files for that program or service.

Pym@example.com¶

RdW@example.com¶

Mister@example.com¶

Soap@invalid.com¶

Bertie@sample.com¶

At the end of each line in the list, there is a hidden paragraph mark (a pilcrow—¶). You will now turn that into the separator that you need. Highlight the list; click the Replace icon on the toolbar. In the Find and Replace dialog box, click the More>> button, then the Special button, and choose the first item on the list—Paragraph Mark. The Find what: box now has the code for the pilcrow in it, ^p. In the Replace with: box, type a semicolon and a space (or whatever your mail program needs). Click Replace All.

You will be asked if you want to continue searching from the beginning of the document. Click No. There you have it, all ready for use:
Pym@example.com; RdW@example.com; Mister@example.com; Soap@invalid.com; Bertie@sample.com;

Now, just cut and paste that new list into the To: box in your e-mail program. There will be an extraneous semicolon (or whatever your separator is) at the end; delete it.

If your original list was in Excel, simply paste the column you want to use into Word and pick up at the Replace step.

If you receive the list in Word, and would like it in Excel, simply highlight the table, copy it, then click in a cell in your worksheet, and press the down-arrow under Paste and choose Special, then Text.

If you just want to get that contact information from Word into Excel, save the Word file as Text, then open an Excel workbook, click the Data tab, and in the first section of the ribbon (Get External Data), choose From Text. Click on the file name and the Import button, and step through the Text Import Wizard. If you can read the text in the first window, just click Next, choose the delimiter (in our sample, the commas), click Next, and leave the data format as General. Click Finish.

Each program in Office has its strengths; in some cases, the task you want to perform is best left to a different program. Word and Excel can talk to each other easily using plain text documents as an intermediary. When you’re trying to clean something up, think about the process, then use the best tool, even if it isn’t the one you’ll ultimately put the content in.

Posted in: Microsoft Office Tips

Leave a Comment (0) ↓
Site Navigation
  • IT Solutions
  • Business IT Solutions
  • Contact Us
  • Tech Tips Blog