We can copy and paste tables into Excel from the web, apps such as Word, and PDF files. We can thus save time and effort by avoiding retyping the data. When we copy tables from such sources, the data in each table cell is pasted in an individual cell on the worksheet.
After pasting such tables, we need to clean them up so that we can take advantage of Excel’s calculation capabilities. Cleaning may involve removing unwanted spaces, changing text values into numeric values, or applying the correct format to dates.
This tutorial looks at four methods we can use to copy and paste table data from Word to Excel.
We will use the following Word Table of products and suppliers to demonstrate how this can be achieved.
|Supplier ID||Product Name||Product Code||Brand|
Method 1: Use Copy and Paste commands
In this method, we first copy the Word table and paste it into Excel.
We use the following steps:
- Select the Word table by pointing to the upper left corner of the table and clicking the four-headed arrow.
- Press Ctrl + C to copy the table. Alternatively, right-click the table and select Copy from the shortcut menu.
- Open the Excel worksheet and select any blank cell and ensure that the surrounding paste area is empty so that we do not replace any existing data. Press Ctrl + V to paste the table. Alternatively, right-click the cell and select Paste from the Ribbon.
We get the pasted table:
- Apply the necessary formatting to the table, for example increasing the width of the columns to accommodate the data.
We end up with a formatted table displayed below:
Method 2: Copy and Paste to match destination formatting
If you have predefined formatting for a table in your worksheet, and you need to retain the formatting in the copied Word table use the following steps:
- Select the Word table and press Ctrl + C to copy the table.
- Select any blank cell in the formatted worksheet. Ensure that the paste area is empty so that we do not replace any existing table. Right-click the cell and select the Match Destination Formatting paste option.
We get a table that conforms to our predefined formatting:
Method 3: Use the Convert to Text and Text to Columns features
Sometimes we may want to first convert the Word table into text before pasting it to Excel.
We use the following steps:
- Select the Word table.
- Click Layout >> Data >> Convert to Text.
- Select a delimiter in the Convert Table To Text dialog box, in this case, we have selected Commas, and click the OK button.
The table is converted to text as displayed below:
- Save the Word document containing the table as a .txt file by clicking File >> Save As and selecting Plain Text from the Save as type drop-down list in the Save As dialog box and click the Save button.
- Open the saved file in Notepad, select the data, and press Ctrl + C to copy it.
- Open the worksheet in which to paste the data. Select a blank cell and ensure that the paste area is empty so that no existing data is replaced. Press Ctrl + V to paste the data.
All the data is pasted into one column.
- With the data still selected click Data >> Data Tools >> Text to Columns.
- In the first step of the Convert Text to Columns Wizard dialog box, select the appropriate file type that best describes your data, in this case, we choose Delimited because in our data it is commas that separate each field. Then click the Next button.
- In the second step of the Convert Text to Columns Wizard dialog box, set the delimiters that your data contains. We have selected Comma because our data is delimited by commas. We can see how our data is affected by the selection in the Data preview area at the bottom of the dialog box. Click the Next button.
- In the third step of the Convert Text to Columns Wizard dialog box, select each column and set the Data Format as needed. We can also choose a different area of the worksheet to paste the data by changing the cell reference in the Destination box. In this case, we leave the default settings as they are because they are appropriate for our data. Click the Finish button.
The data is separated into different columns as displayed below:
We then can adjust the column widths to accommodate the data and make all the data visible.
Method 4: Copy and Paste the Word table without splitting cells
We will use the following example dataset to demonstrate how to copy and paste a Word table into Excel without splitting cells. Carriage returns have been applied to the dataset in the student column.
When we press the Show/Hide button In Word we can see the paragraph marks in the dataset.
If we use any of the previous methods to copy the table to Excel, we end up with split cells in column A as displayed below because of the carriage returns.
To copy the table without splitting cells, we need to first replace the carriage returns with line breaks. Line breaks move data to the next line without creating paragraphs.
We use the following steps to replace the carriage returns with line breaks:
- Press Ctrl + H to open the Find and Replace dialog box.
- Type ^p in the Find what drop-down control and -line break- in the Replace with drop-down control.
A message box pops up showing how many replacements have happened:
All carriage returns are replaced with -line break- in the Student column.
- Select the table and press Ctrl + C to copy it.
- Open an Excel worksheet and select any blank cell and ensure that all the surrounding cells are empty to avoid replacing any existing data. Press Ctrl + V to paste the table.
- In Excel press Ctrl + H to open the Find and Replace dialog box.
- Type -line break- in the Find what drop-down control and press Ctrl + J in the Replace with drop-down control and click Replace All button.
A message box pops up showing how many replacements have been done.
The table now appears as displayed below:
We finally adjust row heights and column widths to get a table that appears as below:
Pasting tables into Excel saves time and effort.
This tutorial has explained four methods that we can use to paste tables into Excel: use the copy and paste commands, apply copy and paste to match destination formatting, use the Convert to Text and Text to Columns features, and Copy and Paste the Word table without splitting cells.