Content

• To step
• Method 1 of 2: Convert a list
• Method 2 of 2: Convert a table

If you want to move a list or table of data from Word to Excel, you don't have to copy and paste each individual piece of information into its own cell in the spreadsheet. By formatting your Word document correctly first, you can easily import the whole document into Excel with no more than a few clicks.

To step

Method 1 of 2: Convert a list

1. Understand how the document will be converted. When you import a document into Excel, certain characters will be used to determine which data goes into which cell in the Excel spreadsheet. By following a few formatting steps before importing, you can determine what the final spreadsheet will look like and minimize any manual formatting you need to perform. This is especially useful if you are importing a large list from a Word document into Excel.
   • This method works best when you have a list with multiple entries, each organized in the same way (list of addresses, phone numbers, email addresses, etc.)
2. Check the document for any formatting errors. Before you start the conversion, make sure every listing is formatted the same way. This means correcting punctuation errors or correctly ordering all entries that don't match the rest. This ensures that the data is copied properly.
3. Show the formatting marks in your Word document. Displaying the normally hidden formatting characters will help you determine the best way to split the data. You can view them by clicking the "Show / Hide Paragraph Marks" button in the Home tab or by pressing Ctrl+⇧ Shift+*
   • Most lists have either one paragraph character at the end of each line, or one at the end of the line and one on the blank line between entries. You use the markers to insert the characters used by Excel to distinguish between the cells.
4. Replace the paragraph marks between each entry to remove extra space. Excel uses space between items to define the rows, but you will need to delete them now to simplify the formatting process. Don't worry, you'll be putting them back in a minute. This works best when you have one paragraph mark at the end of an entry and one in the space between entries (two in a row).
   • Press Ctrl+H. to open the Find and Replace window.
   • Type ^ p ^ p in the Search field. This is the code for two paragraph marks in a row. If each entry is a single line, with no empty lines in between, use a single one ^ p.
   • Type a separator in the Replace field. Make sure it's not a character that appears elsewhere in the document, such as ~.
   • Click Replace All. You will notice that the entries combine, but that is not a problem now, as long as the separator is in the right place (between each item).
5. Separate each entry into separate fields. Now that your entries are separated so that they appear in consecutive rows, you need to define which data is displayed in each field. For example, if each item has a name on the first line, an address on the second line, and an address on the third line, do the following:
   • Press Ctrl+H. to open the Find and Replace window.
   • Delete one of the ^ pmarks in the Search field.
   • Change the character in the Replace field to a comma ,.
   • Click Replace All. This replaces the remaining paragraph symbols with the comma, which makes each line a field.
6. Replace the separator to complete the formatting process. Once you have done the two search and replace steps above, your list will no longer look like a list. Everything is now on the same line, with commas between each entry. This final search and replace step will make your data back into a list, keeping the commas that define the fields.
   • Press Ctrl+H. to open the Find and Replace window.
   • Type ~ (or whatever character you originally chose) in the Search field.
   • Type ^ p in the Replace field.
   • Click Replace All. This turns the data into individual groups, separated by commas.
7. Save the file as plain text. Now that the formatting is complete, you can save the document as a text file. This will allow Excel to read and process your data, so that it is placed in the correct fields.
   • Click the File tab and select "Save As".
   • Click on "Save as type" and select "Plain text".
   • Give the file a name of your preference and click Save.
   • If the file conversion window appears, click OK.
8. Open the file in Excel. Now that the file has been saved as plain text, you can open it in Excel.
   • Click the File tab and select Open.
   • Click the "All Excel Files" drop-down menu and select "Text Files".
   • Click Next> in the Text Import Wizard.
   • Select "Comma" from the delimiter list. You can see how the data is separated in the preview at the bottom of the window. Click on Next>.
   • Select the data format for each of the columns and click Finish.

Method 2 of 2: Convert a table

1. Create a table in Word with your data. If you have a list of data in Word, you can convert it to a table format in Word and then easily copy it to Excel. If your data is already in table format, continue to the next step.
   • Select all the text you want to convert to a table.
   • Click the Insert tab and then the Table button.
   • Select "Convert Text to Table".
   • Enter the number of lines per data in the "Number of columns" field. If there is an empty line between each entry, add a column to the total.
   • Click OK.
2. Check the layout of your table. Word will create a table based on your settings. Check again that everything is in the right place.
3. Click on the small "+" button that appears in the top left corner of the table. This appears when you hover the cursor over the table. By clicking on this you select all data in the table.
4. Press .Ctrl+C.to copy the data. You can also click the "Copy" button in the Home tab.
5. Open Excel. Once the data has been copied, you can open Excel. If you want to put the data into an existing spreadsheet, load it. Place the cursor in the cell where you want the top left cell of the table to appear.
6. Press.Ctrl+V.to paste the data. The individual cells from the Word table are placed in separate cells in the Excel spreadsheet.
7. Split any remaining columns. Depending on the type of data you are importing, there may be additional formatting you need to make. For example, if you import addresses, the city, state and zip code may all be in the same cell. You can have Excel split up this data automatically.
   • Click on the column header of the column you want to split to select the entire column.
   • Select the "Data" tab and click the "Text to Columns" button.
   • Click Next> and select the "Comma" in the Separator field. If you followed the example above, this will separate the city from the province and the zip code.
   • Click Finish to save the changes.
   • Select the column to be split, and repeat the process, choosing "Space" as the separator instead of "Comma". This separates the province from the zip code.