31

When copying a cell with line breaks in Excel and pasting it into Notepad, the text is pasted onto one line. Why is that?

  • 2
    Hello welcome to superuser! Try to provide as much detail as possible so you can get a more detailed answer to your question. Head over to ask your own question page for handy tips to help you ask the best questions. I tested this with Excel 2013 and Notepad++ and that works fine, but I was able to replicate your described issue with the regular Notepad application that comes with Windows OS and that does not work. – angelofdev Dec 18 '18 at 22:28
  • Note that the clipboard can support multiple flavors of the same snippet (raw text up to lots of metadata about where it came from). It is up to the receiving program to determine which it likes best. Notepad is not very smart. – Thorbjørn Ravn Andersen Dec 19 '18 at 15:32
27

Its due to the nature of how spreadsheets work. Imagine you have a spreadsheet full of data. If you were to copy an entire row of data, then paste it into Notepad, all of that data needs to be on one line. This is because if you were to copy another row of data and paste it, the next row of data needs to be on one line. If there were line breaks in the first row you copied, the data would no longer be in two linear rows. If you were to copy the data out of Notepad and back into the spreadsheet, it would not paste properly.

  • 6
    This answer is wrong. Try saving your notepad after pasting the text with line break. Then open file with wordpad instead. Just a bug in old versions of notepad. Your should mark the other answer as correct. – Zak Dec 19 '18 at 23:34
  • @Zak you are incorrect. This is how pasting from Excel works into any simple text editor. The article you linked, has nothing to do with a bug, but is about Notepad adding support for Unix carriage return and line feeds. – Keltari Dec 20 '18 at 6:39
  • 4
    @Keltari Actually no, @Zak is correct. For some reason when you add a line break in Excel (using Alt+Enter), only 0xA is entered, not the full 0xD 0xA, which means LF, instead of CR/LF. Excel interprets this and show the line break, while the plain notepad doesn't, because it expects CR/LF. It's a difference in formats being detected more than a bug, but still, that is the correct reason. – ChatterOne Dec 20 '18 at 11:51
  • 4
    @Keltari There is nothing to argue. Use a binary editor and see what's written in the file. – ChatterOne Dec 20 '18 at 12:31
  • 1
    @Keltari Honestly these upvotes are mostly the effect from being the accepted answer on a HNQ. You can see how many more people agree with the other answers. – kapex Dec 20 '18 at 23:24
45

This is true when you use Notepad, which is Microsoft's basic text editor that is already pre-installed in Windows.

However, you can use a more advanced text editor such as PSPad or Notepad++ (both excellent, and free), and you get your line breaks transferred.

Excel:

Excel

Cell marked, then copy-paste into Notepad++:

Notepad++

Same content pasted into Notepad (the Windows default editor):

WindowsNotpad

Note that in both cases, quotation marks were added automatically!

The better editors give you also the option to display the control characters such as LineFeed (LF) and CarriageReturn (CR). In Notepad++ this looks like:

Notepad++withcontrochars

As a conclusion: choose your tool depending on your needs. If you need cell boundaries preserved in the editor, but cell contents may be altered a bit, use Notepad. If you need cell contents left untouched including line breaks, and 1:1-reproducibility of cell boundaries is not crucial, use another editor.

Mass processing of such data

If you need both cell borders and cell contents preserved 1:1 at the same time, you may run into problems.

There might be more intelligent solutions, but what I did in such cases was writing a little programme in any language (VBA, Python, or whatever you like most) that reads the contents and adds placeholder strings for the line breaks (something as simple as "###Linebreak###", which can later be re-replaced by CR and LF control characters. Of course, this is fiddly work then and makes sense only if you have to process large amounts of data.

You may run into problems also with the quotation marks that are added. These may at first glance be useful to preserve cell boundaries even when line breaks are included. However, your cell may have quotation marks a part of the original content, and then you get new problems. There are various solutions to this, then, but it needs your attention.

  • 12
    Notepad++ showing CRLF there is actually because it sees the LF and assumes it's supposed to be a full CRLF and converts it. – 3D1T0R Dec 19 '18 at 2:08
  • @3D1T0R I suspect that is because a carriage return without line feed would suffer in the readability. It would be delightfully space efficient though. – Stian Yttervik Dec 19 '18 at 7:57
  • 4
    @3D1T0R: it depends on the Notepad++ line ending setting. It's possible to "binary paste" to keep the original Excel formatting, which is indeed a LF only. – Thomas Weller Dec 19 '18 at 11:15
  • 1
    This should be the accepted answer – ChatterOne Dec 20 '18 at 15:54
  • 1
    Do note that Notepad under Windows 10 version 1809 has the option to use LF as a linebreak (instead of CR-LF). If you use that, there's no converting but linebreaks are still displayed properly. – Erik A Dec 21 '18 at 9:04
34

Keltari's answer gives the logical reasoning, while this answer focuses on the technical difference.

There are three different forms of line breaks in use in computing:

  • Unix and macOS 10.0+ line endings use a line-feed character (LF)
  • Macintosh (prior to macOS 10.0) line endings use a carriage-return character (CR)
  • Windows line endings use a combination of carriage-return and line-feed characters (CRLF)

This is a hold over from the way that type writers work.

Excel uses a combination of these line breaks to represent cells with multiple lines:

  • Cells are separated by the Tab character.
  • Rows are separated by the CRLF characters.
  • Multiline cells separate each line using just the LF character.

This becomes apparent when you save your workbook as a .txt file and open it with a text editor that supports showing these characters.

Excel

Notepad++

Notepad only processes CRLF as newlines and ignores LF or CR by themselves. They're still in the document, but aren't visible in any way.

Note: You will not see this when pasting back and forth, because Notepad++ automatically adjusts line endings for you, while regular Notepad does not.

  • 3
    You should also mention that the LF is actually still present when pasted in notepad, notepad simply doesn't display it visibly. (It renders effectively the same as a zero width space.) Hence why copying the pasted data back out of notepad into Excel includes these line breaks (although Excel doesn't automatically resize rows to them). – 3D1T0R Dec 19 '18 at 1:58
  • 3
    Note that this is only how Excel exports workbooks to text files. For xlsx files, lines breaks within a string are actually represented by CRLF. – kapex Dec 19 '18 at 15:39
  • 3
    Interestingly, it is entirely possible to construct a cell that "really" contains a CRLF - ="A"&CHAR(13)&CHAR(10)&"B". Line breaks produced by formulas are not visible in excel, but work fine if copied and then pasted via "paste values". A value pasted from manually typing "A<CRLF>B" in notepad and copying will also retain the CRLF. – Random832 Dec 19 '18 at 16:09
  • 3
    @3D1T0R is right. Try to copy a single Excel cell with text "a\nb" (where "\n" represents a newline) into Notepad. You will see "ab" in Notepad (with the quotation marks). But if you put the caret in front of "a" and press the Right arrow key on your keyboard three times, you will find that there is an invisible zero-width character between "a" and "b"! Indeed, you can also use the Shift key to select it and copy it to the clipboard! – Andreas Rejbrand Dec 19 '18 at 18:25
  • 2
    @AndreasRejbrand: I have used this technique a number of times to make text files with LF EOLs readable quickly. i.e. Find an LF character, select it, Ctrl+C, Ctrl+Home, Ctrl+F, Ctrl+V, Enter, Esc, Enter. Then repeatedly F3, Enter till end. [Edit: <kbd> doesn't work in comments?] – 3D1T0R Dec 19 '18 at 19:21
1

Excel uses LF to denote new lines within a cell. Notepad does not consider this to be a new-line as Windows in general uses CRLF for new-lines.

Notepad++ is more intelligent than the relatively simple Notepad and thus interprets LF as a new-line also.

  • I don't use Notepad++, but out of curiosity: what happens if you have an Excel table consisting of at least two rows containing a multiline cell and copy-paste that into Notepad++? – Andreas Rejbrand Dec 19 '18 at 18:27
  • 1
    @AndreasRejbrand The multiline cells will be wrapped in " and Notepad++ will normalize all of the line endings to match. See here. If you instead save as .txt and open in Notepad++, the multiline cells will have LF only. See here. – Worthwelle Dec 20 '18 at 18:07

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.