Excel formula: Add a line break with a formula

Generic formula

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

Explanation

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

How this formula works

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 text wrap enabled on cells that contain line breaks.

Author

Dave Bruns

Related formulas

Count line breaks in cell

To count total lines in a cell, you can use a formula based on the LEN, SUBSTITUTE, and CHAR functions. In the example, we are using this formula: = LEN ( B5 ) - LEN ( SUBSTITUTE ( B5 , CHAR ( 10 ), "" )) + 1 How the formula works First,...

Add line break based on OS

To add a line break taking into account the current OS (Mac or Windows), you can use the INFO function to test the system and then return the correct break character — CHAR(10) for Windows, CHAR(13) for Mac. How this formula works The character used...

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.