Cfrtjryk

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

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.

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.

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

This is indeed so when you use Notepad, i.e. Microcosoft's basic text editor delivered with 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 transfered.

Excel:

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

Same content pasted into Notepad (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.