Explanation

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.

Note: “Wrap text” must be enabled for Excel to display the line breaks.

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.

Explanation

In older versions of Excel (before Excel 2016?), the character used for line breaks is different depending on whether Excel is running on a Mac or Windows computer: On Windows Excel, the line break character is ASCII 10. In older versions of Excel on a Mac, the line break character is ASCII 13. These are invisible characters and therefore difficult to enter directly into a formula. The standard way to insert them in a formula is to use the CHAR function like this:

CHAR(10) // line break in Win Excel
CHAR(13) // line break in Mac Excel

Because the line break varies by platform in older versions of Excel, it is tricky to write a single formula that will work as expected on both platforms. One solution is to use the INFO function to test the current environment and then set a value for a line break that is conditional on the platform. In the worksheet shown, we do this by first naming cell C3 “break”. Then, in the same cell, we enter the following formula:

=IF(INFO("system")="mac",CHAR(13),CHAR(10))

Now we can use the word break like a variable in a formula. If Excel is running on a Mac, break will equal CHAR(13), if not, break will equal CHAR(10). In column E, we can then concatenate the address information that appears in B, C, and D with a formula like this:

=B6&break&C6&break&D6

The result of the concatenation is text with line breaks:

Traci Brown¬ 1301 Robinson Court¬ Saginaw, MI 48607

Note: to see the line break take effect, you will need to enable text wrap .