Add a line break with a formula

In this example, the goal is to join together three text values separated by line breaks. In Excel, you can use the keyboard shortcut Alt + Enter to add a line break in a cell that contains text, but the same approach won't work in a formula. The trick is to use the CHAR function with the ASCII code 10 like this:

=CHAR(10) // line break

CHAR (10) returns a hidden character that Excel uses as a line break. To use CHAR(10) in a formula you must use concatenation.  The article below explains two approaches.

With TEXTJOIN

One way to join together text values with a line break is to use the TEXTJOIN function, which is designed to concatenate values together with a delimiter of your choice. This is the approach seen in the worksheet above, where the formula in cell F5 is:

=TEXTJOIN(CHAR(10),1,B5:D5)

The inputs to TEXTJOIN are provided as follows:

• delimiter - given as CHAR(10) which returns a line break character in Excel
• ignore_empty - set to 1, to avoid adding extra line breaks when values are empty
• text1 - the range B5:D5, which contains the three text values to join together

As the formula is copied down, it inserts a line break character after Name and Address like this:

Traci Brown¬
1301 Robinson Court¬
Saginaw, MI 48607

Manual concatenation

It is also possible to create the same result with "manual" concatenation using the ampersand (&) operator like this:

=B5&CHAR(10)&C5&CHAR(10)&D5

Notice that each cell reference must be joined to the line break manually with an ampersand (&). The result from this formula is exactly the same as the formula above:

Traci Brown¬
1301 Robinson Court¬
Saginaw, MI 48607

Note: make sure you have Wrap Text enabled on cells that contain line breaks.