Excel Char For New Line Download This Sample

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

But sometimes while doing this we need to add a line break between the text which we want to combine.

As you know there are serval methods to concatenate text but when it comes to adding a line break we need to use a specific formula for this.

Heres the deal: To insert a line break between text we need to use CHAR function.

And in todays post, Im going to show you exactly 3 different formulas which you can use to have a line break while combining values from different cells.

Excel Char For New Line Download This Sample

Make sure to download this sample file from here to follow along.

And now, lets get started.

Formulas to Concatenate with a Line Break By simply using an Ampersand () with CHAR By using CHAR with CONCATENATE Function Combination of CHAR and TEXTJOIN In all these formulas there is one thing which is commonly we have used Thats the CHAR function.

This function returns a specific character according to the number you have specified in it.

You can learn more about CHAR from here, and now, lets move on to our line break formulas.

Get Better at Excel in 2018 A bundle of E-Books you need to thrive in Excel this year.

Yes, Send me Now 1.

By simply using an Ampersand with CHAR This is actually a simple formula in which you need to refer to all the cell which you want to combine but by using CHAR(10) between those cell references.

Look at this formula below: A2CHAR(10)B2CHAR(10)C2 Important Note: Once you enter this formula in a cell make sure to apply Wrap Text to that cell.

First, we have A2 and then a line break, after that B2 and again a line break and in the end, C2.

Get a Line Break using CHAR with CONCATENATE Function This formula is almost the same as we have used above but here instead of using an ampersand () we have simply used CONCATENATE function.

Learn more about CONCATENATE from here and the formula should be like below: CONCATENATE(A2,CHAR(10),B2,CHAR(10),C2) Again in this formula, make sure to apply Wrap Text to the cell.

Value from cell A2, line break using CHAR, Values from cell B2, line break and in the end, value from cell C2.

Combination of CHAR and TEXTJOIN to Get a Line Break within Text TEXTJOIN is the advanced version of CONCATENATE.

In the function, you can use a delimiter to combine text from cells.

And here is the formula to get a line break while concatenating text from different cells.

TEXTJOIN(CHAR(10),TRUE,A2:C2) heres how this formula works In the formula, we have used CHAR as a delimiter and then used TRUE to ignore empty cells.

And in the end, selected the entire range A2:C2 which we need to combine.

Now what happens is, as we have specified CHAR(10), it will add a line break after every cell value.

Sample File download this sample file from here to follow along.

Conclusion While using a line break in a formula the two things you need to take care is using CHAR function for the line break and applying Word Wrap to the cell.

From all the above formulas which we have learned here, I believe using TEXTJOIN is the simple and the best way.

Mac Excel Char For New Line