In Excel, when we normally combine text from different cells using CONCATENATE. But sometimes, while doing this, we must add a line break between the text we want to combine. As you know, there are several methods to combine cells, but when adding a line break, we need to use a specific formula.
Here’s the deal: We need to use the CHAR function to insert a line break between text.
In today’s post, I will show you exactly 3 different formulas you can use for a line break while combining values from different cells.
And the best part is all these 3 formulas are simple to use. In all these formulas, there is one thing we commonly use. That’s the CHAR function. This function returns a specific character according to the number you have specified.
To enter a line break, we need to mention 10 if you use the Excel Windows version and 13 if you use the Excel MAC version. You can learn more about CHAR from here. Now, let’s move on to our line break formulas.
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.
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 can refer to the cells or text you want to join inside the line break. Edit the cell and enter the formula below in it.
=CONCATENATE(A2,CHAR(10),B2,CHAR(10),C2)
Again, apply “Wrap Text” to the cell.
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, Values 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 here is the formula to get a line break while concatenating text from different cells.
=TEXTJOIN(CHAR(10),TRUE,A2:C2)
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 the TEXTJOIN like the following formula:
=TEXTJOIN(CHAR(10), TRUE, IF(A2:C2<>"", A2:C2, ""))In this formula, IF(A2:C2<>””, A2:C2, “”) checks each cell in the range A2 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.
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)This formula is a little limited as compared to the TEXTJOIN function. You should only use it when you can’t have the option to use another formula.
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.
=CombineWithLineBreak(A2:C2)Function CombineWithLineBreak(rng As Range) As String
Dim cell As Range
Dim result As String
result = ""
For Each cell In rng
If cell.Value <> "" Then
result = result & cell.Value & vbLf
End If
Next cell
' Remove the last line break if it exists
If Len(result) > 0 Then
result = Left(result, Len(result) - 1)
End If
CombineWithLineBreak = result
End FunctionTo 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.
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 “Word Wrap” 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.
Ive been trying to put spaces between the Concatenated lines in my excel (MAC) But i am failing to do this it keeps kicking out my formula please help..
I actually had to open VBA code with ALT+F11 and enter the following in the Worksheet_Calculate()
Private Sub Worksheet_Calculate()
dim i as integer
for i = 1 to 6 step 2
me.cells(i,2).Value = me.Cells(i,1) & Chr(10) & me.Cells(i+1,1).Text
Next i
End Sub
Then I go back to Worksheet and press F9 so it will Calculate sheet, therefore running the Sub, which takes the first Row, First Column (A1) and adds chr(10) and adds Second Row, First Columns, and enters into First Row, Second Column and repeats 3 times
Hi Puneet.
I’ve been concatenating for ages, but love the textjoin.
If im combining multiple cells (eg A2 and C2) to make line 1, and then combining A3 and C3 on the next line in the same cell, am I correct in assuming I must continue to use concatenate, since i dont want every cell reference on a separate line?
Thanks
Annette
Hi Puneet, Thanks for your page! I am looking for a formula that rounds cells to an even number. is this possible?
Use EVEN function, Claire.
Hi Puneet,
I have no words, how to thank you for the great tips that you are sharing. Concatinate with line break, it helped me and my team so much in completing one of our project that came with y/day deadline.
Thanks a ton.
?
CHAR(10) did not leave. a space, CHAR(2) did leave a space.
I have Excel 2016, therefore, no TEXTJOIN function
How to center as you illustrated is beyond me.
Alt+Enter is an easy way to create a line break
But, if you have a data already and need to put it in once cell Alt+Enter doesn’t work I guess. Example you have a data customer master where customer name, Address1, Address2,City, and pincode in different col and if your try this would really make sense. Isnt’ it?
textjoin isn’t a valid function in 2013…
sample excel downloaded file is missing
TEXTJOIN didn’t work for me and wasn’t an option in ‘FORMULAS’, is this only available in later versions of Excel? (currently using 2013), the first of the 3 seemed the easiest to remember and that’s the one i’ll probably use going forward, thank you.
Yup 🙁