26

I want to copy the following:

line1


line 2


line 3

into a single cell in Excel.

I know that I can use the combination Alt+Enter on Windows and Ctrl+Alt+Enter on Mac to create a line break in the cell, but here I want to copy paste in a single cell, not typing its content.

Here is what I get:

enter image description here

I want it in a single cell with line breaks:

enter image description here

26

To paste multi-line content in Excel, all you need to do is double click the new cell (so you see the arrow 'thing' blinking), and paste (ctrl + V):

enter image description here

|improve this answer|||||
  • 1
    Sadly, this does NOT work in Excel 2013. You have to paste into the editor strip above the cell display area. – Carl Witthoft Jan 20 '17 at 13:22
  • 1
    This works with Office 2016. – WendiT Nov 8 '19 at 23:53
11

if you have to do this for more than one cell, replace all linebreaks in word (^p or ^l) with something you can replace (i use LINEBRK). then paste into excel, and replace all LINEBRK with alt+0010. re-word wrap.

|improve this answer|||||
  • 3
    Note for other n00Bs like me: to enter that into the REPLACE box, hold down <ALT> key and type 0010 using the numeric keypad. – Carl Witthoft Jan 20 '17 at 13:38
  • I couldn't get alt+0010 to work, so I ended up using a formula in a new column. I also had to enable word wrap on the column. =SUBSTITUTE(A1, "LINEBRK", CHAR(10)) – indiv Aug 2 '18 at 19:50
  • Upvote because it works in bulk (the chosen answer only works for a single cell). This works even if you want to insert a lot of text into Excel, even though it is a bit more work. – Cerno Oct 12 '19 at 11:23
5

I had the same problem now.
I found a different (in some cases a better) solution.
If you can, surround the clipboard content with quotation marks:

"line1


line 2


line 3"
|improve this answer|||||
  • Interestingly it does not work if you copy it from the browser like this (I assume due to formatting information copied along with the text data). So if you want to try this, copy it to a text editor first, then copy it from there and drop it to Excel. That works. – Cerno Oct 12 '19 at 11:21
0

Here's an "interesting" "funny" thing about pasting like this in Excel...

The choices currently "set" in the Data|Text to Columns functionality in the Ribbon change how Paste puts this data into a cell.

Select some data, even a single cell, and click in the Ribbon to start that function. The dialogue box appears and choose Delimited, then Next. Now you have the list of possible delimiters Excel expects adn the ability to specify a nice "Other" if you'd like. If the only one checked is Tab, you will be able to paste the kind of data you mention into a single cell with those individual bits on different lines in that cell, just as you wish here. If, for instance, Space is checked, you will see the data you wanted on separate lines in a single cell broken into wholely separate cells like you show. It goes more mutli-cells, right to left, for spaces, but I've seen it go multi-line as well with internet data.

Why? Um, who knows? Surprisingly, not many even seem to have seen this either. But basically, clear ALL those checkboxes, not hard to check them again if you really DO want to do Text to Columns, so, you know... just clear them. Then try the pasting and I'll give odds it goes just fine.

All those other approaches work too, they just require some to a LOT of extra work and other programs and so on. Use the easiest if the above does not help this time.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for?Browse other questions tagged or ask your own question.