In Excel, when we normally combine text from different cells using CONCATENATE.

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.

Here’s the deal:

To insert a line break between text we need to use CHAR function.

And in today’s post, I’m going to show you exactly 3 different formulas which you can use to have a line break while combining values from different cells.

And the best part is all these 3 formulas are simple to use.

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

And now, let’s get started.

## 3 Formulas to Concatenate with a Line Break

In all these formulas there is one thing which is commonly we have used

That’s the CHAR function.

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

And to enter a line break we need to mention 10 if you are using Excel Windows version and 13 if you are using Excel MAC version.

You can learn more about CHAR from here, and now, let’s 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.*

### 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:

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

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

#### here’s how this formula works…

Actually, in this formula, we have combined a total of five values by using an ampersand (&).

Three cell values and two CHAR functions for line breaks.

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

**Related Tutorial**: How to Remove Extra Spaces in Excel

### 2. 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.

#### here’s how this formula works…

In this formula, we have combined 5 different things using CONCATENATE.

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

### 3. 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. You can learn more about it from here.

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 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.

I hope you found this formula tip useful, but you need to 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 and make sure to share this tip which your friends as well.

**You must Read these Next**

- Remove First Character from a Cell: With this formula, you can simply remove the first or (n) character(s) from a text from a cell and…
- Count Number of Words in Excel: When it comes to Excel there is no such option to count words. But with these formulas you can not only...
- Add Leading Zeros in Excel: Whenever you try to insert a zero before a number, Excel removes it and you’ll only get the number starting from...
- Count Cells with Text: In Excel, we have different formulas which we can use to count all the cells where we have text values. But the point is...
- Get First and Last Word: In this post, I’d like to share with you how to get first and the last word from a text string by using a formula...
- Count Unique Values in Excel: For this, you need to use a method which will count value only one time and ignore all the other occurrences...
- Sentence Case Formula: In sentence case, for an entire sentence, the first letter of the first word should be in the capital case and the rest of the...
- Star Rating Formula: To create this star rating template all you need is REPT function. REPT Function is useful when you want to...
- Change a Formula into its Result Value: Sometimes it’s important to change a formula into its result value and I’ve found that there are...

About the Author

Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can ﬁnd him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.

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 🙁