How to Convert Word to Excel

Understand how documents are converted. When you import a document into Excel, certain characters are used to determine what data goes into each cell in the Excel spreadsheet. By going through a few formatting steps before importing, you’ll be able to control how the final spreadsheet will appear and minimize the manual formatting you have to do. This is especially useful if you import a large number of lists from a Word document into Excel.

• This method works best when you have a list of multiple items, each of which is formatted the same (list of addresses, phone numbers, email addresses, and so on).

Scan the document for formatting errors. Before starting the conversion process, you must make sure that each item is formatted the same. This means you need to correct all punctuation errors or arrange any items that don’t fit the rest. This ensures that the data is transferred correctly.

Display formatting characters in Word documents. Displaying commonly hidden formatting characters will help you determine the best way to divide items. You can show them by clicking the “Show / Hide Paragraph Marks” button in the Home tab or pressing Ctrl + ⇧ Shift + *

• Most lists have one paragraph mark at the end of each line, or one at the end of each line, and one in a blank line between items. The paragraph mark is a means of inserting characters used by Excel to distinguish between cells.

Replace the paragraph mark between each entry to remove extra space. Excel will use the spacing between items to determine the line, but for now you need to remove this factor to aid in the formatting process. Don’t worry, we’ll add it back soon. This works best when you have one paragraph mark at the end of a section and another in the space between items (two marks in a line).

• Press Ctrl + H to open the Find and Replace window.
• Enter ^p^p in the Find field. Here is the code for the case of two paragraph marks in one line. If each entry is a separate line and there are no blank lines between them, use the code ^p .
• Enter the separator character in the Replace field. You need to make sure it’s not a character that can appear anywhere in the document, such as a ~ .
• Click Replace All . Items can combine on their own but this shouldn’t be a bother, as long as the delimiter is in the right place (between each item)

Separate each item into its own field. With the items separated so that they can be displayed in rows later, you now need to define what data to display in each field. For example, if each entry is a name on the first line, home address on the second line, and country and postal code on the third line, you could

• Press Ctrl + H to open the Find and Replace window.
• Remove one of the ^p paragraph marks in the Find field.
• Change the character in the Replace field to comma , .
• Click Replace All . The remaining paragraph symbols will be replaced with commas to separate each line into a field.

Replace the delimiter to complete the formatting process. After completing the two find and replace steps above, your list will no longer look like a list. All information will still be on the same line, but there will be a comma between each piece of data. This final find and replace step returns the data to the list layout but retains the commas to identify the fields.

• Press Ctrl + H to open the Find and Replace window.
• Type ~ (or whatever character you chose at first) in the Find field.
• Enter ^p in the Replace field.
• Click Replace All . Entire items will be split back into separate groups and separated by commas.

Save as plain text file. Once the formatting is done, you can save the document as a text file. This will allow Excel to read and parse the file so that the data is put 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 however you like and click Save .
• If the File Conversion window appears, just click OK .

Open the file in Excel. After saving the file as plain text, you can open the document in Excel. ^[1]

• Click the File tab and select Open.
• Click the “All Excel Files” drop-down menu and select “Text Files”.
• Click the Next > sign on the Text Import Wizard window.
• Select “Comma” from the Delimiter list. You can see how the items are separated in the preview at the bottom. Then click Next > .
• Select the data format for each column and click Finish .