Excel Show Line Feeds in Excel 2013

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.

Contents

• Insert line breaks manually with Alt + Enter
• Insert line breaks with a formula
• How to search for new lines in Excel
• Replace or remove new lines with the search function
• Replace or remove line breaks with the SUBSTITUTE function
  • Also interesting:

Insert line breaks manually with Alt + Enter

Inserting a line break in Excel is quite easy: Just press Alt + Enter to 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))

How to search for new lines in 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 + f on the keyboard.
2. Type Ctrl + j into the search field and press Enter .

That's it.

Replace or remove new lines with the search function

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 + f on the keyboard.
2. Type Ctrl + j into the search field and press Enter .
3. As the "Replace with" value enter a space character.
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

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.

Post navigation

stonegeorted.blogspot.com

Source: https://professor-excel.com/line-breaks-in-excel/

Share this post