How to combine cells in excel


Introduction

We have already learned how to merge cells in Excel . But as we saw that all the methods discussed in that tutorial  were useful only for merging empty cells and by merging non-empty cells we lose data. If we come across situations where we are required to combine the values within the cells instead of merging the cells physically, then we have different solutions for that. In this tutorial we'll be discussing two methods of combining cells in excel and also different scenarios in which we can use these methods. The first method is to combine Excel cells by using the Ampersand ("&") and the second method is by using the CONCATENATE function. Here we'll discuss both these methods in detail, so let's get going.

 

Method 1 :  Combining cells in Excel using the Ampersand (“&”) Formula

Below are the steps involved in combining the cells in Excel sheet by using the Ampersand operator.

 

Step 1 :

Construct the formula - Suppose we want to combine two cells - cell 1 and cell 2, the address of cell 1 is “C5” and the address of cell 2 is “E5”.

 

How to combine cells in excel

 

Then our formula will be  :   =C5&E5

 

How to combine cells in excel

 

Step 2 :

Enter the Above formula in the cell in which you want to get the final combined result.

e.g. if we want to get the final result in the cell G5 then Enter the above formula in the cell G5.

 

How to combine cells in excel

 

Step 3 :

If we want to combine a number of values at one go, e.g. all values in column A to be combined with all corresponding values in column B and get the result in column C then enter the formula =A1&B1 in C1

 

How to combine cells in excel
Step 4 :

Copy and paste this in all the cells of column C.

 

How to combine cells in excel

 

Method 2 : Combining cells in Excel using the Concatenate Method

This method is very similar to the first method, just that it makes use of an Excel function – Concatenate. Below are the steps involved in using this method to combine Excel cells.

 

Step 1 :

Construct the formula - Suppose we want to combine two cells - cell 1 and cell 2, the address of cell 1 is “C5” and the address of cell 2 is “E5”.

 

How to combine cells in excel

 

Then our formula will be  :   =CONCATENATE(C5,E5)

 

How to combine cells in excel
Step 2 :

Enter the Above formula in the cell in which you want to get the final combined result.

e.g. if we want to get the final result in the cell G5 then Enter the above formula in the cell G5.

 

How to combine cells in excel

 

Step 3 :

If we want to combine a number of values at one go, e.g. all values in column A to be combined with all corresponding values in column B and get the result in column C then enter the formula = CONCATENATE(A1,B1) in C1

 

How to combine cells in excel

 

Step 4 :

Copy and paste this in all the cells of column C.

 

How to combine cells in excel

 

Different Scenarios for Combining Cells in Excel :

We have already learned the different methods to combine cells in Excel. Now its time to apply these techniques in real life scenarios. Below are some of the scenarios where we'll be using the methods we have just learned.

Scenario 1 :

Combining values without space in between -

Formulae Method 1 :  =C5&E5

Formula Method 2 :    =CONCATENATE(C5,E5)

 

How to combine cells in excel

 

Scenario 2 :

Combining values with space in between –

Formulae Method 1 :  = C5&” “&E5

Formula Method 2 :    = CONCATENATE(C5,” “,E5)

 

How to combine cells in excel

 

Scenario 3 :

Combining values with separator in between. Suppose our separator is “;“

Formulae Method 1 :  = C5&”;“&E5

Formula Method 2 :    = CONCATENATE(C5,”;“,E5)

 

How to combine cells in excel

 

Scenario 4 :

Combining values with text in between. Suppose our text is “is the capital of”

Formulae Method 1 :  = C5&” is the capital of “&E5

Formula Method 2 :    = CONCATENATE(C5,” is the capital of “,E5)

 

How to combine cells in excel

 

Scenario 5 :

Combining values with Line breaks in between.

We have a detailed tutorial on combining cells in excel with line breaks in between. But here are the final formula to be used.

Formulae Method 1 :  = C5&CHAR(10)&E5

Formula Method 2 :    = CONCATENATE(C5, CHAR(10),E5)

 

Scenario 6 :

Combining text and numbers with formatting.

 

Conclusion :

This one was a very useful tutorial as it solves a lot of problems that we come across in our day to day life while using excel e.g. we may be required to combine the first name and last name to create a new column of full name, we may come across situations where we need to join values in two different columns seaparated by a speacial character like hyphen. We might face such situations while creating IDs etc. Combining cells with line breaks in between is also very important and is used frequently. Hence this tutorial will come in handy in various situations and should be learned thoroughly.