Why does Notepad ignore line breaks

Why are Excel line breaks not transferred to the editor?

When you copy a cell with line breaks in Excel and paste it in Notepad, the text is pasted on one line. Why this?


This is due to the way spreadsheets work. Imagine you have a table full of data. If you want to copy an entire row of data and paste it into Notepad, all of the data must be on one row. If you copy and paste another row of data, the next row of data must be on a row. If the first line copied contains line breaks, the data is no longer on two linear lines. If you copy the data from the editor to the worksheet, it will not be pasted correctly.

This is the case when you use Notepad, the Microsoft standard text editor that comes pre-installed in Windows.

However, you can use an advanced text editor like PSPad or Notepad ++ (both excellent and free) and transfer your line breaks.

Excel :

Cell highlighted, then copy and paste in Notepad ++ insert :

The same content used in Notepad inserted (the standard Windows editor):

Note that quotation marks were automatically added in both cases!

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

Conclusion: choose your tool according to your needs. Use the editor when you want to keep the cell boundaries in the editor, but the cell contents may change slightly. Use a different editor if the cell content including line breaks should remain untouched and the 1: 1 reproducibility of the cell boundaries is not critical.

Mass processing of such data

Problems can arise when both cell borders and cell contents have to be maintained 1: 1 at the same time.

There may be smarter solutions, but in such cases I wrote a small program in any language (VBA, Python, or whatever your favorite language) that reads the content and adds wildcard characters for the line breaks (something as simple as " ### Linebreak ### ", which can later be replaced by CR and LF control characters. This is of course cumbersome and only makes sense if you have to process large amounts of data.

You may also have problems with the added quotation marks. At first glance, these can be useful for preserving cell boundaries, even if they contain line breaks. However, your cell may contain quotation marks as part of its original content and you will encounter new problems. There are several solutions to this, but you have to take care of it.

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

There are three different forms of line break used in arithmetic:

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

This is a disadvantage of the way writers work.

Excel uses a combination of these line breaks to display cells with multiple rows:

  • Cells are separated by the character.
  • Lines are separated by the characters.
  • Multi-line cells separate each line with just the character.

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

Notepad only processes a line break and ignores it or by itself. They are still in the document, but are not visible in any way.

Note: You won't see this when you paste back and forth as Notepad ++ will automatically adjust the line endings to suit your needs, whereas normal Notepad cannot.

Excel uses LF to mark new rows within a cell. Notepad doesn't consider this a new line because Windows generally uses CRLF for new lines.

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

We use cookies and other tracking technologies to improve your browsing experience on our website, to show you personalized content and targeted ads, to analyze our website traffic, and to understand where our visitors are coming from.

By continuing, you consent to our use of cookies and other tracking technologies and affirm you're at least 16 years old or have consent from a parent or guardian.

You can read details in our Cookie policy and Privacy policy.