Download Article
Download Article
If you need to move a list or table of data from Word into Excel, you don't have to copy and paste each individual piece of information into its own cell in the spreadsheet. By properly formatting your Word document first, you can easily import the entire document into Excel with just a few clicks.
Steps
-
Understand how the document will be converted. When you import a document into Excel, certain characters will be used to determine what data goes into each cell in the Excel spreadsheet. By performing a few formatting steps before importing, you'll be able to control how the final spreadsheet appears and minimize the amount of manual formatting you have 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 of multiple entries, each formatted the same (list of addresses, phone numbers, email addresses, etc.).
-
Scan the document for any formatting errors. Before beginning the conversion process, you'll want to ensure that each entry is formatted in the same way. This means fixing any punctuation errors or reorganizing any entries that don't match the rest. This will ensure that the data transfers properly.Advertisement
-
Display the formatting characters in your Word document. Displaying the normally hidden formatting characters will help you determine the best way to split up the entries. You can display them by clicking the "Show / Hide Paragraph Marks" button in the Home tab or by pressing Ctrl + ⇧ Shift + *
- Most lists will either have one paragraph mark at the end of each line, or one at the end of the line and one in the blank line between entries. You will be using the marks to insert the characters used by Excel to differentiate between cells.
-
Replace the paragraph marks between each entry to get rid of extra space. Excel will use space between entries to determine the rows, but you'll need to get rid of it for now to help the formatting process. Don't worry, you'll be adding it back in a little bit. 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 into the Find field. This is the code for two paragraph marks in a row. If each entry is a single line and there are no blank lines between them, use a single ^p instead.
- Enter a delimiting character into the Replace field. Make sure that it isn't a character that appears anywhere in the document, such as ~ .
- Click Replace All . You'll notice that the entries may combine themselves, but that's not a concern right now as long as the delimiting character is in the right place (between each entry)
-
Separate each entry into separate fields. Now that your entries are separated so that they appear in subsequent rows, you'll want to define what data will appear in each field. For example, if each entry is a name on the first line, a street address on the second line, and a state and zip code on the third line, 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 to a comma , .
- Click Replace All . This will replace the remaining paragraph symbols with the comma separator, which will separate each line into a field.
-
Replace the delimiting character to finish the formatting process. Once you've done the two Find-and-Replace steps above, your list will not look like a list anymore. Everything will be on the same line, with commas between every piece of data. This final Find-and-Replace step will return your data to a list while keeping the commas that define the fields.
- Press Ctrl + H to open the Find and Replace window.
- Enter ~ (or whatever character you chose originally) into the Find field.
- Enter ^p into the Replace field.
- Click Replace All . This will break your entries back into individual groupings separated by the commas.
-
Save the file as a plain text file. Now that your formatting is complete, you can save the document as a text file. This will allow Excel to read and parse your data so that it goes in the correct fields.
- Click the File tab and select "Save As".
- Click the "Save as type" drop-down menu and select "Plain Text".
- Name the file as you prefer and click Save .
- If the File Conversion window appears, just click OK .
-
Open the file in Excel. Now that you've saved the file in plain text, you can open it up in Excel. [1] X Research source
- 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 window.
- Select "Comma" in the Delimiter list. You can see how the entries will be separated in the preview at the bottom. Click Next > .
- Select the data format for each of the columns and click Finish .
Advertisement
-
Make 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 quickly copy that table into Excel. If your data is already in table format, skip down to the next step.
- Select all of the text that you want to convert into a table.
- Click the Insert tab and then click the Table button.
- Select "Convert Text to Table".
- Enter the number of lines per record in the "Number of columns" field. If you have a blank line between each record, add one to the total.
- Click OK .
-
Check the formatting of your table. Word will generate a table based on your settings. Double-check it to ensure that everything is where it should be.
-
Click the little "+" button that appears in the upper-left corner of the table. This will show up when you're hovering the mouse over the table. Clicking this will select all of the data in the table.
-
Press . Ctrl + C to copy the data. You can also click the "Copy" button in the Home tab.
-
Open Excel. Once the data has been copied, you can open Excel. If you want to put the data in an existing spreadsheet, load it up. Place your cursor in the cell that you want the upper-left cell of the table to appear.
-
Press . Ctrl + V to paste the data. The individual cells from the Word table will be placed into separate cells in the Excel spreadsheet.
-
Split any remaining columns. Depending on the type of data you are importing, you may have some additional formatting you need to do. For example, if you are importing addresses the city, state abbreviation, and zip code may be all in the same cell. You can have Excel split these up automatically. [2] X Research source
- Click the column heading of the column you want to split to select the whole column.
- Select the "Data" tab and click the "Text to Columns" button.
- Click Next > and then select the "Comma" in the Delimiters field. If you are using the above example, this will separate the city from the state abbreviation and zip code.
- Click Finish to save the changes.
- Select the column that still needs to be split and repeat the process, selecting "Space" instead of "Comma" as the delimiter. This will separate the state abbreviation from the zip code.
Advertisement
Expert Q&A
Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
Advertisement
Video
Tips
Submit a Tip
All tip submissions are carefully reviewed before being published
Name
Please provide your name and last initial
Thanks for submitting a tip for review!
References
About This Article
Thanks to all authors for creating a page that has been read 221,438 times.
Advertisement
