How to Combine cells in Excel with line breaks in between


Introduction :

We have already learned how to combine cells in excel with a separator in between. Line breaks are a special case of separators and needs a little bit of extra understanding hence we have dedicated a separate tutorial on this topic.

When we combine cells simply as discussed in the previous tutorial – either by using the ampersand operator or by using the Concatenate function – all the cell values are joined in one line. Suppose we are working with cells that will combine to form an address, then we’ll get the whole address in one line which is not readable. Even if we use a separator like hyphen “-“ then also the address will not be in proper format. In such cases we need to insert a line break in between the values to give a proper format to the result. The final value that we’ll get will be in a single cell but each element of the address will be on a separate line within the cell.

 

Combining Excel cells along with line break character :

To use the line break functionality in Excel we need to make use of the function CHAR(10). CHAR(10) is the line break character. Below are the steps to combine cells in excel using the line break.

 

Method 1 : Using the Ampersand (&) :

The first method to combine Excel cells with line breaks in between is by using the Ampersand operator -

Step 1 :

Construct the formula – Suppose the individual elements to be combined are from column A to column D -  then our formula will be

=A1&CHAR(10)&B1&CHAR(10)&C1&CHAR(10)&D1

 

Step 2 :

Enter this formula in cell E1

 

How to Combine cells in Excel with line breaks in between

 

Step 3 :

Go to Home Tab and click on "Wrap Text" in Alignment Group.

 

 

How to Combine cells in Excel with line breaks in between

 

The Concatenated text starts to appear with line breaks in beween.

 

How to Combine cells in Excel with line breaks in between

 

Step 4 :

Copy and paste this formula in all the cells of column E.

 

Method 2 : Using the concatenate Function :

The second method that we'll discuss to combine cells in excel is by using the concatenate function :

Step 1 :

Construct the formula – Suppose the individual elements to be combined are from column A to column D -  then our formula will be

=CONCATENATE(A1,CHAR(10),B1,CHAR(10),C1,CHAR(10),D1)

 

Step 2 :

Enter this formula in cell E1

 

How to Combine cells in Excel with line breaks in between

 

Step 3 :

Go to Home Tab and click on "Wrap Text" in Alignment Group.

 

How to Combine cells in Excel with line breaks in between

 

The Concatenated text starts to appear with line breaks in beween.

 

How to Combine cells in Excel with line breaks in between

 

Step 4 :

Copy and paste this formula in all the cells of column E.

 

Conclusion :

While filling data which needs formatting for proper display - adding line breaks is of utmost importance. Hence it becomes very important to know  different ways of adding line breaks while combining different cells in excel. In this tutorial we have learned two different ways of doing this, one is by using the ampersand operator and another by using the concatenate function.