If you need to move a list or table of data from Word to Excel, you don't have to copy and paste all the individual information into the cells of a spreadsheet. If you format your Word document first, you can easily import the entire document into Excel with just a few clicks.
Steps
Method 1 of 2: Convert a List
Step 1. First you need to understand how the document will be converted
When you import a document into Excel, certain characters are used to determine what data goes into each cell of the spreadsheet. By taking a few formatting steps before importing, you'll be able to control the final appearance of your sheet and minimize the manual formatting you'll need to perform. This is especially useful if you are importing a large list from a Word document into Excel.
This method is useful when you have a list of multiple items, all formatted in the same way (list of addresses, telephone numbers, email addresses, etc.)
Step 2. Scan the document for formatting errors
Before starting the conversion process, you will need to make sure that each entry is formatted the same way. This means correcting all punctuation errors or rearranging entries that are not identical to the others. This will ensure that your data is transferred correctly.
Step 3. View formatting characters in Word document
Making the formatting characters, which are usually hidden, visible will help you determine the best way to split entries. You can do this by clicking the "Show / Hide Formatting Symbols" button on the Home tab or by pressing Ctrl + ⇧ Shift + *.
Most lists will have a paragraph mark at the end of each line, or one at the end of the line and one in the blank line between items. You will use signs to enter the characters used by Excel to distinguish between cells
Step 4. Replace the paragraph marks between each entry to remove the extra space
Excel will use the space between the entries to determine the rows, but you'll need to get rid of it for now to make the formatting process easier. Don't worry, you'll be putting them back in shortly. Follow this advice if you have a paragraph mark at the end of each entry and one in the space between the entries (two consecutive).
- Press Ctrl + H to open the Find and Replace window.
- Type ^ p ^ p in the Find field. This is the code for two consecutive paragraph marks. If each entry is a single line and there are no blank lines between them, use a single ^ p instead.
- Enter a delimiting character in the Replace field. Make sure it's a character that doesn't appear anywhere else in the document, such as ~.
- Click on Replace All. You will notice that the entries may join, but you don't have to worry if the delimiting characters have been inserted in the right places (between each entry).
Step 5. Separate each entry into a single field
Now that you have separated the entries so that they appear in the following lines, you will need to define which data will appear in each field. For example, if each entry has a name in the first line, an address in the second, postcode and country in the third, you can:
- Press Ctrl + H to open the Find and Replace window.
- Remove one of the ^ p marks in the Find field.
- Change the character in the Replace field with a comma,.
- Click on Replace All. You will replace the remaining paragraph marks with a separating comma which will divide each line into a field.
Step 6. Replace the delimiting character to finish the formatting process
Once you have performed the previous two replacement steps, the list will no longer look like a list. Each entry will be on the same line, with commas between each data. This last substitution step will return the data in a list, without losing the commas that define the fields.
- Press Ctrl + H to open the Find and Replace window.
- Enter ~ (or the character you initially chose) in the Find field.
- Enter ^ p in the Replace field.
- Click on Replace All. You will divide the entries into individual groups, but they will be separated by commas.
Step 7. Save the file as a text file
Now that you've completed the formatting, you can save the document as a text file. This will allow Excel to read and process the data so that it fits into the right cells.
- Click on File and choose Save As.
- Click on the "Save as type" drop-down menu and select "Text only".
- Give the file any name you like and click Save.
- In the File Conversion window that appears, click OK.
Step 8. Open the file in Excel
Now that you have saved the file as text only, you can open it with Excel.
- Click on the File tab and select Open.
- Click on the "All Excel Files" drop-down menu and select "Text Files".
- Click Next> in the Text Import Wizard window.
- Select "Comma" in the Delimiter list. You can see how the entries will be separated in the preview below. Click on Next>.
- Select the data format for each column and click Finish.
Method 2 of 2: Convert a Table
Step 1. Create a table in Word with your data
If you have a list of data in Word, you can convert it to table format in Word and then quickly copy that table to Excel. If the data is already formatted in a table, skip to the next step.
- Select all the text you want to convert to table.
- Click the Insert tab and then click the Table button.
- Select "Convert Text to Table".
- Enter the number of columns per item in the "Number of Columns" field. If there is a blank line between each entry, add one to the total.
- Click OK.
Step 2. Check the formatting of the table
Word will generate a table based on your settings. Double check it to make sure everything is where it needs to be.
Step 3. Click on the small "+" button that appears in the upper left corner of the table
It will appear when you hover over the table. By clicking on this button you will select all the data in the table.
Step 4. Press
Ctrl + C to copy the data.
You can also click the "Copy" button on the Home tab.
Step 5. Open Excel
Once the data is copied, you can open Excel. If you want to insert data into an existing sheet, open it. Put the cursor in the cell you want to match the upper left cell of the table.
Step 6. Press
Ctrl + V to paste the data.
The individual cells of the Word table will be copied into separate cells of the Excel sheet.
Step 7. Divide the remaining columns
Depending on the type of data you are importing, you may need to format the table further. For example, if you are importing addresses, the city, state, and postcode may all be in the same cell. You can have Excel split this data automatically.
- Click on the title of the column to split to select it all.
- Select the "Data" tab and click the "Text in Columns" button.
- Click on Next> and then select the "Comma" in the Delimiters field. If you are using the above example, this will separate the city from the province and postcode.
- Click Finish to save the changes.
- Select the column to be split and repeat the operation, selecting "Space" instead of "Comma" as the delimiter. You will separate the province from the postcode.
