Formatting concatenated data in Excel can be frustrating when text runs together horizontally, cluttering your reports. While standard formulas like standard concatenation merge cells side-by-side, they fail to structure data vertically. Utilizing the CHAR(10) function grants you the power to insert clean, readable line breaks directly within your merged strings.

Stipulation: This visual break only renders correctly if the "Wrap Text" feature is enabled on the destination cell. For instance, combining an Address, City, and State using =A2&CHAR(10)&B2 creates a perfectly stacked mailing label. Below, we outline the exact step-by-step formulas to master this formatting technique.

Excel Formula To Concatenate Cells With Line Break

When working in Microsoft Excel, you often need to combine data from multiple cells into one. Whether you are preparing mailing addresses, generating clean descriptions, or compiling lists, merging text is a fundamental skill. However, a simple merge can result in a messy, continuous string of text that is difficult to read.

To make your data visually appealing and structured, you need to separate your merged inputs using line breaks (also known as carriage returns or line feeds). This guide will walk you through the various ways to concatenate cells with a line break in Excel, ranging from classic operator methods to advanced, modern Excel formulas.

The Key Ingredient: Understanding the CHAR Function

Excel does not allow you to simply press Enter inside a formula to create a line break. Instead, you must use a specific function that tells Excel to output a line break character. This is achieved using the CHAR() function, which returns a character based on its ASCII code number.

• On Windows: Use CHAR(10) (Line Feed).
• On Mac: Use CHAR(13) (Carriage Return) or CHAR(10). In modern versions of Excel for Mac, CHAR(10) is generally the standard and works seamlessly.

Throughout this article, we will use CHAR(10) as our universal line break character.

Method 1: Concatenating with the Ampersand (& ) Operator

The ampersand (& ) is the traditional and most common way to join strings in Excel. It is highly flexible and works in every version of Excel ever released.

The Formula Syntax

=Cell1 &
CHAR(10) &
Cell2 &
CHAR(10) &
Cell3

Step-by-Step Example: Formatting a Mailing Address

Imagine you have a customer database structured like this:

To combine these details into a single, standard mailing label block in cell D2, write the following formula:

=A2 &
CHAR(10) &
B2 &
CHAR(10) &
C2

Result (with Wrap Text enabled):

Method 2: Using the CONCATENATE and CONCAT Functions

If you prefer using built-in functions over operators, Excel offers functions dedicated to joining strings.

Using CONCATENATE (Excel 2013 and older)

The CONCATENATE function is a legacy function. While it is still supported for backward compatibility, it does not support range selections, meaning you must specify each cell individually.

=CONCATENATE(A2, CHAR(10), B2, CHAR(10), C2)

Using CONCAT (Excel 2016 and Newer / Microsoft 365)

The CONCAT function replaced CONCATENATE. It behaves similarly but is more robust. However, because it joins everything continuously, you still need to manually insert the line break character between each cell reference:

=CONCAT(A2, CHAR(10), B2, CHAR(10), C2)

Method 3: The TEXTJOIN Function (The Ultimate Solution)

Introduced in Excel 2016 (and available in Office 2019, Office 2021, and Microsoft 365), TEXTJOIN is by far the most efficient and elegant formula for concatenating cells with a line break.

The beauty of TEXTJOIN is that it allows you to define a single; this case, CHAR(10)) and apply it automatically between all selected cells in a range. Even better, it offers a built-in option to ignore empty cells, preventing unsightly double line breaks in your final output.

The Formula Syntax

=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

How to Write It

To join cells A2 through D2 using a line break while ignoring any blank fields, use this formula:

=TEXTJOIN(CHAR(10), TRUE, A2:D2)

Why TEXTJOIN is Superior: Handling Empty Cells

Consider a scenario where you are merging columns: First Name, Middle Name, and Last Name. Some contacts do not have a middle name.

• If you use the ampersand method (=A2 & CHAR(10) & B2 & CHAR(10) & C2) and B2 is blank, your final result will contain an empty, awkward blank line in the middle.
• If you use TEXTJOIN(CHAR(10), TRUE, A2:C2), Excel will completely skip the blank middle cell and seamlessly join the First and Last names with a single line break.

Practical Use Case: Creating a Bulleted List in a Single Cell

You can combine TEXTJOIN with manual characters to build clean lists out of row or column data. For example, if you want to turn a horizontal range of tasks (cells A2 to E2) into a vertical bulleted list in a single cell, you can prepend a bullet point character to your formula.

The ASCII code for a standard bullet point (•) is 149. Thus, CHAR(149) represents a bullet point. We can also add a space after it for formatting.

To combine your task list with bullet points and line breaks, use the following formula:

="• " & TEXTJOIN(CHAR(10) & "• ", TRUE, A2:E2)

How this works:

1. It starts the string with a physical bullet point and a space: "• ".
2. It joins the cells in the range A2:E2 using a; consists of a line break followed by a bullet point and a space: CHAR(10) & "• ".
3. The result is a perfectly formatted, multi-line bulleted list contained entirely in one cell.

Troubleshooting & Frequently Asked Questions

1. Why is my formula displaying a generic error?

Double-check your parentheses and quotes. If you are using the ampersand method, make sure you have an & on both sides of every CHAR(10). For instance, =A1 & CHAR(10) B1 will trigger an error. It must be written as =A1 & CHAR(10) & B1.

2. My line break shows up as a square box symbol. Why?

This occurs when Excel doesn't recognize the character encoding correctly, or more commonly, when Wrap Text is turned off. If you turn on Wrap Text and the square symbol remains, verify that your computer is using standard line breaks. On Windows systems, try swapping CHAR(10) with CHAR(13) or combining them: CHAR(13) & CHAR(10).

3. Can I export these line breaks to other applications?

Yes. If you copy a cell containing line breaks generated via Excel formulas and paste it into Word, Outlook, or a text editor (like Notepad), the line breaks will carry over perfectly. Note that pasting into some plain-text applications might automatically wrap the entire pasted block in double quotation marks.

Summary: Which Method Should You Use?

The approach you choose depends entirely on your version of Excel and your specific dataset structure:

For most modern Excel users, TEXTJOIN is the absolute gold standard for concatenating cells with line breaks. It saves time, minimizes formula length, and automatically handles messy data anomalies like empty cells.