How to Concatenate with a Line Break in Excel

Using an Ampersand with CHAR to Combine with a Line Break

This is a simple formula in which you must refer to all the cells you want to combine using CHAR between those cell references.

CHAR helps you get a character based on a number you provide. You can specify a code where each code stands for a specific character. This can be useful when inserting a line break with a formula. Look at this formula below:

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

Important Note: Once you enter this formula in a cell, apply “Wrap Text” to that cell.

Here’s how this formula works…

In this formula, we combined five values using an ampersand (&): three cell values and two CHAR functions for line breaks. First, we have A2 and then a line break, then B2 and again a line break, and finally, C2. If B2 is empty, you get two line breaks in a row and an empty line in the cell; TEXTJOIN (below) avoids this.

Related Tutorial: Remove Extra Spaces in Excel

Using CHAR with CONCATENATE Function

This formula is almost the same as the one we used above, but instead of using an ampersand (&), we have used the CONCATENATE function.

CONCATENATE joins text from different cells into one cell. Its syntax is simple: CONCATENATE(text1, text2, …). You refer to the cells or text you want to join, with CHAR(10) between them. Edit the cell and enter the formula below in it.

Again, apply “Wrap Text” to the cell.

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

CONCATENATE still works in all versions, but Microsoft now recommends CONCAT, which replaces it in Excel 2019 and later.

Here’s how this formula works…

In this formula, we have combined five different things using CONCATENATE: the value from cell A2, a line break using CHAR, the value from cell B2, a line break, and, in the end, the value from cell C2.

Combination of CHAR and TEXTJOIN to Get a Line Break within Text

TEXTJOIN is the advanced version of CONCATENATE. You can use a delimiter to combine text from cells in the function, and it needs Excel 2019 or later, or Microsoft 365. And here is the formula to get a line break while concatenating text from different cells.

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

Apply “Wrap Text” to the cell to see each value on its own line.

Here’s how this formula works…

In the formula, we used CHAR as a delimiter and TRUE to ignore empty cells. And in the end, we selected the entire range A2:C2, which we must combine. As we have specified CHAR(10), it will add a line break after every cell value.

Add a Condition with TEXTJOIN

You can also create a condition to combine values from various cells and add a line break within. For this, you need to use IF within TEXTJOIN, like the following formula:

=TEXTJOIN(CHAR(10),TRUE,IF(A2:C2<>"",A2:C2,""))

In Excel 2019, confirm it with Ctrl + Shift + Enter, as it’s an array formula; in Microsoft 365 and Excel 2021, Enter is enough.

In this formula, IF(A2:C2<>””, A2:C2, “”) checks each cell in the range A2:C2 to see if it is not empty. If a cell is not empty, it returns its value; otherwise, it returns an empty string. Then, TEXTJOIN(CHAR(10), TRUE, IF(A2:C2<>””, A2:C2, “”)) combines the non-empty values from A2 to C2, adding a line break between each value. As TRUE already skips empty cells, this exact condition gives the same result as the plain TEXTJOIN formula; replace it with your own test, like IF(A2:C2>100,A2:C2,””), to combine only the values that meet it.

Use CONCAT with CHAR Function

The CONCAT function combines the values from multiple cells into one. Using CHAR(10) with the values adds line breaks between them.

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

CONCAT is available in Excel 2019 and later, and Microsoft 365.

This formula is a little limited as compared to the TEXTJOIN function. You should only use it when you can’t use TEXTJOIN, as it doesn’t skip empty cells or add the delimiter for you.

Create a Custom Function to Add Line Break with a Formula

If you frequently combine cells with a line break in your work, I suggest you use a macro to create a custom function that refers to the range. It will combine all the values from the range and add a break in between.

Here’s the code for the function:

Function JoinWithBreak(rng As Range) As String
    Dim c As Range, s As String
    For Each c In rng
        If c.Value <> "" Then
            If s <> "" Then s = s & vbLf
            s = s & c.Value
        End If
    Next c
    JoinWithBreak = s
End Function

To use this function in the worksheet, press Alt + F11 to open the VBA editor. Then, click Insert > Module to insert a new module. Paste the above code into the module, go back to the sheet and use it like any other function (and apply Wrap Text to the cell). Save the file as a macro-enabled workbook (.xlsm) to keep the function.

=JoinWithBreak(A2:C2)

The function skips empty cells and puts a line break (vbLf, the same as CHAR(10)) between the values.

Conclusion

When using a line break in a formula, you need to take care of two things: using the CHAR function for the line break and applying “Wrap Text” to the cell. From all the above formulas we have learned here, I believe using TEXTJOIN is the simplest and best way.

I hope you found this formula tip useful, but you must tell me one thing now.

Which formula do you think is the best to use? Share your views with me in the comment section. I’d love to hear from you. Also, make sure to share this tip with your friends.

Frequently Asked Questions