Posted on Leave a comment

Excel – enter a new line break in a cell and remove the existing one

Enter a new line break with a keyboard shortcut

When preparing a report in Excel, sometimes you need to type a longer text. If a text is too long, it doesn’t fit in the cell, and wrapping the text does not improve its readability. A good solution would be to insert a new text line in the cell.

While typing just press ALT and ENTER keys on your keyboard to enter a new line break in a cell. Mac users should use OPTION COMMAND RETURN keys.

Notice that after entering a new line of text in a cell the Wrap text icon is on.

Remove a line break

To remove text line breaks in a cell, you should unwrap the text by clicking Wrap text icon. If this solution doesn’t work, use the formula described below.

Enter a new line break with a formula

To enter a new line break, you can use CHAR formula. This formula returns a character for a given ASCI character code. For example, if you type CHAR(36), the formula returns the $ sign. To return line break with CHAR formula you need to use code 10 on Windows and code 13 on Mac.

Here is a short example of entering lorem ipsum text with line breaks.

=”Lorem ipsum”&CHAR(10)&”Lorem ipsum”&CHAR(10)&”Lorem ipsum”

Instead of using & sign you can also use embed CHAR formula in CONCAT or CONCATENATE function.

=CONCAT(“Lorem ipsum”,CHAR(10),”Lorem ipsum”,CHAR(10),”Lorem ipsum”)

After typing formulas, don’t forget to wrap the text and adjust the cell’s width and height.

Remove line breaks using a formula

To remove line breaks with a formula, you must substitute line break character with a space character. Embed CHAR formula in SUBSTITUTE formula.

The arguments of SUBSTITUTE formula are:
Text – text in which you make changes, mostly it is a cell reference
Old_text – a string that is to be replaced. In our example, it is line break expressed with CHAR(10) formula
New_text – a new string that replaces the old one. In this example it is space.
The complete formula looks like this:

=SUBSTITUTE(A1,CHAR(10),” “)

Leave a Reply

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