Exceljet

Quick, clean, and to the point

Add a line break with a formula

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 
Excel formula: Count line breaks in cell
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...
Excel formula: Add line break based on OS
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...
Excel formula: Double quotes inside a formula
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
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, and can be used to add a line break to text in...

Related videos 
How to concatenate with line breaks
In this video we demo how to join different values together in one cell with line breaks. This also includes a clever way to add spaces and commas without using double quotes.

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.

500 Formulas | 101 Functions

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.