How to convert Word to Excel

What decides it is how the data sits in the document. Turn on Show/Hide on Word's Home tab to see the tabs and paragraph marks between the values, and pick the row that matches.

Microsoft's page covers Word and Excel on Windows; on a Mac, copy and paste works the same way with Cmd+C and Cmd+V. Microsoft says the data in each Word table cell "is pasted in an individual cell on the worksheet", so the table keeps its columns.

Microsoft's steps:

Say a partner sends a Word document with a four-column table of contacts: Name, Company, Email and Phone, with a header row and six people under it. Copied and pasted at A1, the header lands in row 1 and each person in a row of their own, one value per cell. The first three rows look like this:

When the values in a document are separated by tabs or commas but aren't in a table, Word can make the table for you. You see the columns in Word, fix anything that's off, and then copy a real table into Excel as above.

Microsoft's advice on the separator: "If you have commas in your text, use tabs for your separator characters." A company name like Harbor Lane Foods, Inc. would otherwise split in two. Each paragraph mark starts a new row. The steps:

Text that isn't in a Word table pastes one line per cell, all in column A. A list typed as Maya Collins, Harbor Lane Foods, (415) 555-0142 needs splitting at each comma, and Text to Columns does it in Excel. It's also how you split a pasted column further, such as a full name into first and last.

Microsoft's steps:

For a long document or one big table, an import gives you more control than a paste: you see a preview and choose the delimiter before anything reaches the sheet. Microsoft's Text Import Wizard page puts it plainly: "If your data is in a Word document, you must first save it as a text file."

A saved .txt holds the whole document, so with several tables the import brings in all of them, and the text between them, one after another. To keep them apart, copy the tables one at a time, or convert one table to text, save it and import it, then do the next.

If the data is in a Word table, turn the table into tab-separated text first, so each cell boundary becomes a tab that Excel splits on. The steps, from Microsoft's Word and Excel pages:

Codes are where a Word to Excel move goes wrong without anyone noticing. Microsoft says Excel "automatically removes leading zeros, and converts large numbers to scientific notation, like 1.23E+15". A ZIP code of 02134 turns into 2134, and a 16-digit account number loses its last digits, because Excel keeps only 15 significant digits.

To keep them, the column has to be text when the data arrives. In Text to Columns or the Text Import Wizard, select the column in the Data preview and choose Text under Column data format. With From Text/CSV, click Edit, or Transform Data, in the preview, select the column, go to Home > Transform > Data Type, choose Text, and then Close & Load.

For a few cells typed by hand, an apostrophe in front, as in '02134, tells Excel to treat the entry as text. Formatting a column as Text afterwards doesn't bring back zeros that are already gone: Microsoft notes it "will not restore leading zeros that were removed prior to formatting." Undo the paste, set the column to Text, and bring the data in again.

Microsoft warns that after a paste "there may be unwanted extra spacing in cells, numbers may have been pasted as text rather than as numeric values that you can calculate, or dates are not displayed correctly." Here is each problem, why it happens and the fix.

An enquiry or a filled-in form is often written as one field per line, such as Name: Maya Collins, then Company: Harbor Lane Foods, then Email: maya.collins@harborlane.example. To turn one of these into a row by hand, paste the lines into column A, then run Text to Columns with Other set to a colon, so the labels land in column A and the values in column B. Copy the values, right-click the cell where the row should start, and choose Transpose from Paste Options. Each value keeps the space that followed its colon, so run =TRIM on the row. A value with its own colon in it, such as a time, splits again, so check those.

When the same kind of document keeps arriving, email it to an Operelio folder instead. Each folder has its own email address, and a .docx attached to an email lands in the folder as a CSV of its rows. Field lines like the ones above become one row with a column for each field. A Word table becomes a row for each line of the table, with a Source column naming the table. Only the first table is read, with any table after it under the same header; a different table is left out, so send one list per document. Anything else becomes one row with the text in a Notes column: free text isn't split into fields.

An enquiry sent as the email itself, with no file attached, becomes one lead: field lines fill columns, and any other email keeps its text in Notes. Only .docx files are read: an old .doc attachment is refused, and the folder's notification asks you to save it as .docx or PDF and send it again. Folders are on Starter and above, and a trail that watches the folder can clean the rows and push them to HubSpot, Salesforce or Pipedrive.