54

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 4

52

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:

8
  • This answer (with trivial, cosmetic differences) appeared on Microsoft.com at Retain multi-line cells when pasting Word table into Excel.  And, BTW, I find that (Alt)+010 is good enough; I don’t need the two leading zeros. Apr 30, 2015 at 21:10
  • @G-Man Ha...if only I'd found that earlier! Too bad I wasn't working in Office 2007 at the time. Also, do you mean the leading one zero (Alt+010) or the leading two (Alt+10)?
    – nikodaemus
    May 1, 2015 at 17:03
  • Thanks - I got all of the steps to work on a Mac (in Excel 15) except for the last one - I was unable to get the alt-0010 or line break entered. Any suggestions? I tried copying-and-pasting a line break from another source as well as a few other tricks. Jun 17, 2016 at 14:46
  • 2
    Thanks. Maybe worth adding that if you find it difficult to get the ALT+1234 numeric codes In Excel, you can alternatively copy and paste the pilcrow symbol into the Search box, and in the Replace box press the Ctrl + Shift + J keys simultaneously to get a line break.
    – TooTone
    Mar 24, 2021 at 21:45
  • 1
    @TooTone — Great tip, Ctl+Shft+J is the only way to go in Windows using the Find/Replace dialog if you don't have a numeric keypad. Most modern laptops no longer support the numeric keypad on the middle alphabetic keys via fn+Alt+[letter keys corresponding to the digits for the character]. But the Google Sheets/Libre Office Calc methods are so much simpler than the one given in this answer.
    – Joan Eliot
    Apr 20, 2022 at 16:02
27

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
  • 7
    ... or... just leave it in Drive :) Jul 20, 2016 at 2:36
  • I had to copy from Word to Google Doc and then from Google Doc to Google Sheet (or else the line breaks would disappear). +1 anyway Sep 20, 2018 at 14:04
  • 1
    Oh my bad! You redressed my tonnes of hecks ushering this cute technique.Long live GOOGLE and you.
    – Learner
    Aug 17, 2020 at 15:55
5

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
  • 1
    Works fine to get in LibreOffice Calc. And then to get from Calc to Excel: 1. select your Calc table 2. Copy 3. In Microsoft Excel, Paste Special as DIF Jun 26, 2019 at 0:52
  • 1
    I've found that if you have character formatting within cells (eg. selective bolding), you need to paste as RTF into Calc. Then in Calc you can save the file in Excel format, which you can then correctly open in Excel.
    – Lebenita
    Mar 14, 2022 at 9:51
0

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .