3 Quick & Easy ways to Concatenate Excel With A Line Break

Ever faced a situation when you are combining text from multiple cells with line break as separator?

Looking for a new fix or hack for this? Keep reading!

CONCATENATE Excel Function

What does it do?

Joins two or more text strings into one string. The item can be a text value, number, or cell reference.

Formula breakdown:

=CONCATENATE(text1, [text2], [text3], …)

What it means:

=CONCATENATE(the first text, the second text, and so on…)

---

CONCATENATE Excel function joins two or more text strings into one string.  The item can be a text value, number, or cell reference.

If you add a double quotation with a space in between ” ” then this will add a space between the texts selected on either side.

Line Break in Excel

Line Break in Excel is added to end the current line and start a new line within the same cell. A long text with a line break and without a line break is shown below.

In general, when you are typing a text in Excel and you need to add a line break, you can simply press Alt + Enter and Excel will take you to the new line within the same cell.

But what if you want that line break when you are trying to combine texts from different cells using a formula.

This is exactly what we will cover in the next section!

3 ways to Excel Concatenate with Line Break

Want to add a line break in between each text string?

This is done by entering the CHAR( ) function in between each text string/argument.

CHAR function returns a character specified by a number from 1 to 255. For example, CHAR(10) returns a line break in windows, CHAR(34) returns a ” (double quote), CHAR(64) returns @, etc.

So, by simply adding CHAR(10) in between your formula you can enter a line break. You will need to select WRAP TEXT in order to see each text on a separate line.

Follow the step-by-step tutorial below to see how easy it is to add a line break in Concatenate Excel using employee data on the example below. Don’t forget to download the Excel workbook and follow along:

 download excel workbook Concatenate.xlsx

 

METHOD 1: Use Concatenate Excel Formula

STEP 1: We need to enter the CONCATENATE Excel function in a blank cell:

=CONCATENATE(

STEP 2: The CONCATENATE arguments:

text1, [text2], [text3], …

Which text do you want to join together?

Let us select all the columns:

=CONCATENATE(A12, B12, C12, D12)

 

Now let’s add the function CHAR(10) to add a line break between each text

=CONCATENATE(A12, CHAR(10), B12, CHAR(10), C12, CHAR(10), D12)

Apply the same formula to the rest of the cells by dragging the lower right corner downwards.

 

STEP 3: Go to Home > Alignment > Wrap Text to show the text in multiple lines and you now have all of the results!

This is how you can add a new line in excel concatenate formula. Let’s look at the other two methods as well!

 

METHOD 2: Use & (Ampersand) Sign

Instead of joining text using the Concatenate function, you can use the Ampersand (&) sign. The & sign works as a concatenation operator and clues together text for different cells as well as the char function.

The formula to combine text with line break in the previous example with & sign will be:

=A9 & CHAR(10) & B9 & CHAR(10) & C9 & CHAR(10) & D9

 

 

METHOD 3: Use TEXTJOIN formula

This function is available in Excel 2019 & Office 365 only.

Instead of selecting individuals cells and adding a CHAR function again and again in the formula, you can use the advanced versions of concatenate function – TEXTJOIN.

TEXTJOIN can be used to concatenate a range of cells using a delimiter.

Excel requires three arguments for TEXTJOIN function:

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

1. Delimiter – The character you need to insert in between each text.
2. Ignore_empty – It should be TRUE if you want to ignore empty cells and FALSE if you want to include the empty cells.
   By default, the value will be TRUE.
3. Text – Range of cells you want to combine.

So, TEXTJOIN function in our example will look like this:

=TEXTJOIN(CHAR(10), TRUE, A10:D10)

Conclusion

In this article, you have gone through the 3 quick ways to combine text from different cells with a line break. You can use either a concatenate function or a & sign or TEXTJOIN function.

Make sure to apply Wrap Text to the cell when you are using Excel Concatenate new line!