How do I copy Word tables into Excel without splitting cells into multiple rows?

I have data in a Word table that includes line and paragraph breaks. When I copy the data into Excel, it splits each line and paragraph I want into multiple cells. How do I copy the data to Excel and keep the breaks?

4 Answers

You'll have to do a bit of character replacement. It's a pretty simple fix.

In Word:

1. Select your entire table in Word.
2. Open the "Find and Replace" dialog (e.g., by typing Ctrl+H).
3. In the "Find what" field, enter ^l. This will select all line breaks.
   • You may select paragraph breaks by entering ^p.
4. In the "Replace with" field, enter ^v.
   • This is a shortcut for the paragraph symbol ¶, also known as a "pilcrow".
   • You may want to replace paragraph marks with two pilcrows for ease in replacement later.
5. Click "Replace All".
6. Copy the table data to the clipboard.

In Excel:

1. Paste your table in the desired location in Excel.
2. With the tabular data selected, open the "Find and Replace" dialog (again, Ctrl+H works).
3. In the "Find what" field, enter the following Alt code: Alt+0182. A pilcrow appears.
   • To enter an Alt code, hold down the Alt key as you type the digits on the numeric keypad. It may help to have Num Lock on.
4. In the Replace field, enter the following Alt code: Alt+0010.
   • This code enters in a single line break. Nothing appears, though your cursor may change.
5. Click "Replace All".

References:

7

There is an easier way using Google Docs:

• Open a new spreadsheet at sheets.google.com
• Copy the entire table from Microsoft Word into the Google Spreadsheet.
• Then copy from the Google Sheet into Microsoft Excel.

3

I found that you can use LibreOffice Calc.

1. Select your Word table
2. Copy
3. In LibreOffice Calc, Paste Special as HTML
4. Save in your favorite format

Your table won't be split into multiple cells.

2

I made a vba function to remove newline that caused the cells to split in excel before copying to excel.

sub RemoveNewLinesFromTabelCells(tblnumber as integer)
'remove newline from every cell in the selected table table by table number dim x as long, y as long, columncount as long, rowcount as long columncount = Activedocument.Tables(tblNumber).Range.Columns.Count rowcount = Activedocument.Tables(tblNumber).Range.Rows.Count for x = 1 to rowcount for y = 1 to columncount ActiveDocument.Tables(tblNumber).cell(x,y).Range.Text = Replace(ActiveDocument.TAbles(tblNumber).cell(x,y).Range.Text, Chr(13,"") next y next x
end sub