Why do Excel line breaks not transfer into Notepad?

Question

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

Solution 1

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.

Solution 2

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:

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

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

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:

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.

Solution 3

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.

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.

Solution 4

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.

View more solutions

Comments

[Admin]

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.

[Admin]

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.

[Kyle Dixon]

Fantastic explanation thank you so much for this technical side.

[3D1T0R]

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]

Notepad++ showing CR LF there is actually because it sees the LF and assumes it's supposed to be a full CR LF and converts it.

[Stian Yttervik]

@3D1T0R I suspect that is because a carriage return without line feed would suffer in the readability. It would be delightfully space efficient though.

[Thomas Weller]

@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.

[kapex]

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.

[Random832]

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.

[3D1T0R]

@kapex: And also when copying to clipboard, hence why this question even exists.

[Andreas Rejbrand]

@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]

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++?

[3D1T0R]

@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?]

[Zak]

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.

[Keltari]

@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.

[ChatterOne]

@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.

[Keltari]

@ChatterOne I'm not going to argue with you. As you can see many people agree with my answer

[ChatterOne]

@Keltari There is nothing to argue. Use a binary editor and see what's written in the file.

[ChatterOne]

This should be the accepted answer

[Worthwelle]

@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.

[AaronD]

+1 for the accurate rendering in NP++

[kapex]

@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.

[Erik A]

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.