Line Breaks in Excel: How to Enter, Find & Remove Them

line, break, excel, cell

Dealing with text in Excel can be painful as the formatting is limited within Excel cells. One method of organizing text is to add line breaks. In this article, we’ll explore ways of dealing with new lines in Excel.

Insert line breaks manually with Alt + Enter

line breaks, line, breaks, cell, excel, string, formula, How to Insert a Line Break Inside a Cell

Inserting a line break in Excel is quite easy: Just press Alt computer_key_Alt+ Enter computer_key_Enterto add a line break inside a cell. This keyboard shortcut works the same way on Windows and the Office 2016 for Mac.

Insert line breaks with a formula

Let’s say you want to add a line break within the CONCATENATE formula or the ‘&’ when you combine two text cells. This works with the code CHAR(10).

Example: A1 has a text and B1 as well. You want to combine them but add two breaks between them. The formula would be:

=A1&CHAR(10)&CHAR(10)&B1

Please note: In order to see the line breaks, entered via Excel formulas, you have to activate “Wrap Text”: Just click on the “Wrap Text” button on the Home ribbon.

Activate "Wrap Text" in order to see line breaks entered in Excel formulas (with CHAR(10))
Activate “Wrap Text” in order to see line breaks entered in Excel formulas (with CHAR(10))

How to search for new lines in Excel

search, function, line, breaks, line breaks, excel
Search for line breaks in Excel by typing Ctrl + j into the search field.

You can use the search function in Excel to search for new lines. Follow these steps:

  1. Open the search window by pressing Ctrl computer_key_Ctrl+ f computer_key_Fon the keyboard.
  2. Type Ctrl computer_key_Ctrl+ j computer_key_Jinto the search field and press Enter computer_key_Enter.

That’s it.

Replace or remove new lines with the search function

remove, replace, line, breaks, excel
Remove line breaks in Excel.

Very similar to search for line breaks you can also remove or replace them. Instead of “Find”, use the “Replace” tab of the search window and replace them with a blank space (” “).

  1. Open the search window by pressing Ctrl computer_key_Ctrl+ f computer_key_Fon the keyboard.
  2. Type Ctrl computer_key_Ctrl+ j computer_key_Jinto the search field and press Enter computer_key_Enter.
  3. As the “Replace with” value enter a space character computer_key_Space_bar.
  4. Click on “Replace All” or just “Replace” if you want to go cell by cell.

In some cases, there a space characters at the end of a line. When that happens, you will get a double space. You might want to use the replace function again, but this time replace all double spaces (type 2x space on the keyboard) by just one space.


Do you want to boost your productivity in Excel?

Get the Professor Excel ribbon!

Add more than 120 great features to Excel!


Replace or remove line breaks with the SUBSTITUTE function

formula, substitute, line, breaks, excel
Remove line breaks in Excel with the SUBSTITUE formula.

Of course, replacing or removing new lines is also possible using formulas. The easiest way is to use the SUBSTITUTE formula. If your input text is located in cell B3, the formula looks like this:

=SUBSTITUTE(B3,CHAR(10)," ")
  1. The first part contains the input text. In our case it’s cell B3.
  2. The second part has the text which you want to replace. In our case it’s the line break, given by CHAR(10).
  3. In the third part you provide the replacement. Usually it’s a space character.

As sometimes the lines already end with a space, you might get double spaces. We recommend replacing double spaces by single space characters. Therefore, just use the SUBSTITUTE formula again and wrap it around the existing SUBSTITUTE formula:

=SUBSTITUTE(SUBSTITUTE(B3,CHAR(10)," "),"  "," ")

Image by Free-Photos from Pixabay

Henrik Schiffner is a freelance business consultant and software developer. He lives and works in Hamburg, Germany. Besides being an Excel enthusiast he loves photography and sports.

7 comments

  1. Hi,

    I’m getting inconsistent results with this. I have multiple spreadsheets with line breaks. I’ve also created a few with line breaks myself, for testing.

    But the Ctrl+H function only finds some of them. It completely misses others. What might cause this? Could it be that something looks like a line break but is really something else?

    Thanks and regards,

    Aidan Sweeney

    1. Hi Aidan,

      sometimes people use a lot of space characters in order to achieve line breaks.
      If that’s not the case, can you try to copy the “line breaks” and search/ replace them?

      Best regards,
      Henrik

  2. Hallo.

    Nice article. Do you by chance, know how to insert a linebreak, where the cursor is positioned, just by using ENTER (and not ALT+ENTER).
    I know you have to use some vba-code, but I don’t succeed (Application.onkey and sendkey methods). Should you use the DLLs maybee?

    Regards Kåre

  3. Is it possible to use the conditional formatting when using a line break in its formula? I have a cell where the drop-down value is based on a text with a line break in it. How can I use the conditional formatting to recognize it? Thanks.

  4. Ctrl-J does not work on Excel for Mac. Does anyone knows what’s the equivalent? Cmd-J is not the equivalent. It does nothing.

Leave a comment

Your email address will not be published. Required fields are marked *