How to Combine Cells in Excel

You might be wondering what’s the need of combining cells in Excel when one can simply type the data of both cells to a new Cell. Well, we would say yes you can do it but suppose you have a large dataset consisting of thousands of rows then it would not be an easy task to manually type in the entire data from each cell.

So, to avoid all the manual effort of typing the data we are going to tell you the process of Combining Cells in Excel by using the ampersand sign “&” or by using the formula of Concatenate Cells.

Let us Understand with Example Case

Now let us consider an example case of a Housing Society’s Data. List consists of Name, Surname, Apartment Name, Block, Room No. of all the residents in the Society.

Refer below image


Suppose we need to combine the Name and Surname of the resident in Cell B3 and C3 to a new Cell G3. We will write the formula as =B3&C3 (click on Cell B3, put ampersand sign, click on Cell C3).

This will result into combination of text as “NicolasTheoder” but there is no space in between the text to make it presentable.

Adding Spaces between Combined Cells.

So, to make it readable we will write the formula in Cell G3 as

=B3& ” ” &C3 (click on Cell B3, put ampersand sign, put double quotes with space, ampersand sign and click on Cell C3).

This will result into combination of text as “Nicolas Theoder” (with proper space in between the texts).

Refer below image

Now if you want to combine the names and surname for all entries you do not need to type the formula again. Just hold the cell G3 from bottom right corner and drag towards cell G12. This will copy the formula to all the cells along with corresponding cell references.

Refer below image

Adding text/punctuation in between combined Cells.

In case you wanted to combine the Names & Surname with comma or semi colon or dash in between you may write the formula as

=B4&”,”&C4 or B4&”;”&C4 or B4&”-“&C4 respectively.

Refer below image

Combining Cells along with Custom Text

If you want to combine Cells of Name and Surname along with Salutation in Column H. Then you need to write the formula as

=”Mr./Ms.”&B3&” “&C3 (put = sign on cell H3, put double quotes with text Mr./Ms., put &ampersand sign, click on cell B3, put ampersand sign, put double quotes with space – to give space between name and surname, put ampersand sign &, click on Cell C3).

Refer image below

Combining Multiple Cells

Consider the scenario in which you need to combine Apartment Name, Block and Flat no. from Cells D3:E3:F3 to Cell I3 (Address Column). We will use below formula to combine

=D3&” “&E3&F3

(put equal to sign in Cell I3, Click on Cell D3, put ampersand sign, put double quotes with space, put ampersand sign, click on cell E3, put ampersand sign, click on cell F3).

You can drag the formula to fill in the cell range to I12.

Refer below image

Combining Cells along with Custom Text Before and After Combined Cells

Now consider a scenario where we need to add Custom Text before and after the combined cells. We will combine a custom text “The Address of” with Cell Value H3, Custom Text “is”, Cell Value of I3. We will click on Cell J3 and write formula as

=”The Address Of “&H3&” “&” is “&I3

(put equal to sign in cell J3, enter the custom text in double quotes with space at end, put ampersand sign, click cell H3, put ampersand sign, put double quotes with space, put ampersand sign, type custom text within double quotes with a space at end, put ampersand sign, click on cell I3).

Drag the formula to cell range I12.

Refer below image

Combining Cells along with Formula

Now let us consider a scenario where in we need to combine two cells with one having a formula. In that case, also we will simply use the ampersand sign to combine both cells and result will be displayed accordingly.

Combining Cells Using Concatenate Function

Excel has a built-in function to combine cell values by using function “Concatenate”. You need to put equal to sign in the cell where you want to combine the cells and then type concatenate followed by cell separated by comma within brackets. As you press enter the cell will be combined. You can multiple cells in the same manner.

If we have used the function for combining Surname along with name, then formula would be

=CONCATENATE(C4,B4)

Similarly, if we want to put space between concatenated texts then we will use ampersand sign in between the formula. The formula would look as below =CONCATENATE(C4&” “&B4).

Hope you understand this feature now and do not forget to try out yourself for sample cases as described in the post.

Please comment for any queries you have or any valuable suggestions.

Happy Learning!

Tags: No tags

Add a Comment

Your email address will not be published. Required fields are marked *