九乡新闻网高中教材人教版全套:Converting Word Docs to Excel
来源：百度文库 编辑：九乡新闻网 时间：2024/04/28 11:52:29
Converting Word Docs to Excel
One item I've learned from using computers isthat there is usually more than one way to solve a problem. This weektwo people approached me with a similar problem. They were trying to geta simple, but long address list from Microsoft Word into MicrosoftExcel. One tried to use macros and the other resorted to cut and paste.In each case, I thought a simpler solution involved Word's Search andReplace feature. Here's Part 1 of a two part tutorial.Each user was starting with a list of names and addresses that werein Microsoft Word. I suspect they were some sort of address directory orcontact list. For various reasons, they needed to get the data intoMicrosoft Excel. They wanted one row for each record. The address recordlooked similar to the records below.
James Madison
124 Main St
Anytown, NY 12345
Paula Harris
356 Longtree View
Harper, MA 01073
Before starting, review your list and look for common denominatorsand possible exceptions. In these cases, the records were uniform witheach one consisting of three lines with a blank line in between.
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 row.
1. Copy the text you wish to convert and paste it to a new document.
2. Turn on Paragraph marks using the Standard toolbar button or Ctrl + Shift + *
Notice how two paragraph marks exist between each record. If yourlast record doesn't show tow, you might want to add a line at the end.We'll substitute a unique character as a record delimiter. I like to usethe tilde ~ sign, but you can use any uncommon character. Be carefulnot to use a character that appears in your list.
3. Go to the top of your document. ( Ctrl + Home )
4. From the Edit menu, select Find …
5. Click the Replace tab
6. Click the More button at the bottom. Your dialog will now show additional options.
7. Click the Special button.
8. Select Paragraph Mark from the pop up menu. Repeat this step.
9. Enter in the symbol you wish to use for your record delimiter such as a tilde.
Your Find and Replace dialog should look similar to the one below.
10. Click Replace All .
11. Click Close .
Microsoft Word will give you a count of how many replacements itmade. Don't worry that your formatting looks off and various lines lookcombined.
Defining the Fields
The next part is to define our fields which will be placed in Excelcolumns. Each record had 3 lines which represented: Name, Address andCity, ST and Zip. In this example, we're going use a comma to separatethese fields. We can parse the names and state in Excel later.
1. Go to the top of your document.
2. From the Edit menu, select Replace .
3. Your Find and Replace dialog will have your previous values. Remove one of the paragraph marks sets in the Find what: textbox.
4. In the Replace with: textbox, clear out the tilde and enter a comma .
5. Click Replace All
6. Click Close .
Breaking Apart the Records
Your document probably looks worse, but don't worry as to create youoften need to destroy. Part of this may be word wrap and part of it isour formatting. The next steps will put it into perspective.
1. Go to the top of your document
2. From the Edit menu, select Replace .
3. Your Find and Replace dialog will have your previous values. Remove the paragraph mark in the Find what: textbox and type in a tilde .
4. In the Replace with: textbox, clear out the tilde
5. Click Special
6. Select Paragraph Marks from the pop up menu
7. Click Replace All
8. Click Close .
If you have extra commas or paragraph marks on the last line, you candelete them. If you're really fastidious and don't like the spacebefore the State, you can do another search and replace. Personally, Iwould do this in Excel with ASAP Utilities which is a free add on.
Saving the File
Your document should now have 1 record per line with the fieldsseparated by a comma and ending with a paragraph mark. There will not bea comma between state and zip code.
1. From the File menu, select Save As
2. In the Save As dialog, enter your file name
3. In the Save as type: drop down menu, select Plain Text .
4. 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.
Pulling the File into Excel
The last part is to import our Microsoft Word text file into Excel.
1. Open Excel
2. From the File menu, select Open
3. In the Open dialog, change the Files of Type : entry to Text Files
4. Point to your .txt file.
5. Click Open
6. The Text Import Wizard should start. Keep the default values and click Next .
7. In Step 2, change your Delimiter from Tab to Comma . The screen should adjust to show the fields in columns.
8. In Step 3, you can change the data format for each column or click Finish to accept General format.
Final Tweaks
Chances are you will want to do some minor tweaking. As example, youprobably want to add column labels. Also, if you have US addresses, youmay want to split the last column that has the State and Zip codecombined. You may also want to split the name column into first and lastnames. In our example, this is easy as a space separates the first andlast name or the state and zip code.
To parse a column into multiple columns,
1. Highlight your column
2. From the Data menu, select Text to Columns…
3. Click the Next button on the Convert Text to Columns Wizard
4. In the Delimiters box for Step 2, select Space
5. Click Next
6. Define your data format.
(Note: For zip codes, you may want to change the data format to Text if you have zip codes starting with “0”.)
While these steps may not work exactly for your list, they shouldprovide the basis for creating the records in Microsoft Word. Your listmay be slightly different or include additional items such as emailaddresses. Either way, you could use similar steps to create a documentthat Microsoft Excel can interpret. In Part 2, we will use tables toaccomplish a similar result.
Converting Word Docs to Excel Part 2