Excel formula: Add a line break with a formula

Generic formula

="text"&CHAR(10)&"text"

Summary

To add a line break with a formula, you can use the concatenation operator (&) along with the CHAR function. In the example shown the formula in E4 is:

=B4&CHAR(10)&C4&CHAR(10)&D4

Explanation

This formula "glues together" the pieces of text that appear in B4, C4, and D4 using the ampersand (&) which is the concatenation operator in Excel.

In between each piece of text, the CHAR function appears with the character code 10. The character code for a line break in Excel varies depending on the platform. On Windows, the code is 10 and on a Mac it's 13.

The result of the concatenation is text with line breaks:

Traci Brown¬
1301 Robinson Court¬
Saginaw, MI 48607

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

In Excel 365, both Win and Mac versions of Excel use CHAR(10) as a line break.

Author

Dave Bruns

Related formulas

Count line breaks in cell

First, the LEN function counts total characters in the cell B5. Next SUBSTITUTE removes all "line returns" from the text in B5 by looking for CHAR(10) which is the character code for the return character in Windows. LEN returns the result inside of...

Add line break based on OS

The character used for line breaks is different depending on whether Excel is running on Mac or Windows: CHAR(10) for Windows, CHAR(13) for Mac. This makes it tricky to write a single formula that will work as expected on both platforms. The...

Double quotes inside a formula

To include double quotes inside a formula, you can use additional double quotes as escape characters . By escaping a character, you are telling Excel to treat the " character as literal text. You'll also need to include double quotes wherever you...

Related functions

Excel CHAR Function

The Excel CHAR function returns a character when given a valid character code. CHAR can be used to specify characters that are hard to enter in a formula. For example, CHAR(10) returns a line break on Windows, and CHAR(13) returns a line break on...

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.