How to Quickly Combine Cells in Excel

A lot of times, we need to work with text data in Excel. It could be Names, Address, Email ids, or other kinds of text strings. Often, there is a need to combine cells in Excel that contain the text data.

Your data could be in adjacent cells (rows/columns), or it could be far off in the same worksheet or even a different worksheet.

How to Combine Cells in Excel

In this tutorial, you’ll learn how to Combine Cells in Excel in different scenarios:

  • How to Combine Cells without Space/Separator in Between.
  • How to Combine Cells with Space/Separator in Between.
  • How to Combine Cells with Line Breaks in Between.
  • How to Combine Cells with Text and Numbers.
How to Combine Cells without Space/Separator

This is the easiest and probably the most used way to combine cells in Excel. For example, suppose you have a data set as shown below:

Combine Cells in Excel - Data without separator

You can easily combine cells in column A and B to get a string such as A11, A12, and so on..

Here is how you can do this:

  • Enter the following formula in a cell where you want the combined string:
    =A2&B2
  • Copy paste this in all the cells.

This will give you something as shown below:

Combine Cells in Excel - Result without separator

You can also do the same thing using the CONCATENATE function instead of using the ampersand (&). The below formula would give the same result:

=CONCATENATE(A2,B2)
How to Combine Cells with Space/Separator in Between

You can also combine cells and have a specified separator in between. It could be a space character, a comma, or any other separator.

Suppose we have a dataset as shown below:

Combine Cells in Excel - Data with separator dataset

Here are the steps to combine the first and the last name with a space character in between:

  • Enter the following formula in a cell:
    =A2&" "&B2
  • Copy paste this in all the cells.

This would combine the first name and last name with a space character in between.

Combine Cells in Excel - Result with separator

If you want any other separator (such as comma, or dot), you can use that in the formula.

How to Combine Cells with Line Breaks in Between

Apart from separators, you can also add line breaks while you combine cells in Excel.

Suppose you have a dataset as shown below:

Combine Cells in Excel - Address

In the above example, different parts of the address are in different cells (Name, House #, Street, City, and Country).

You can use the CONCATENATE function or the & (ampersand) to combine these cells.

However, just by combining these cells would give you something as shown below:

Combine Cells in Excel - Address without linebreak

This is not in a good address format. You can try using the text wrap, but that wouldn’t work either.

What is needed here is to have each element of the address on a separate line in the same cell. You can achieve that by using the CHAR(10) function along with the & sign.

CHAR(10) is a line feed in Windows, which means that it forces anything after it to go to a new line.

Use the below formula to get each cell’s content on a separate line within the same cell:

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

This formula uses the CHAR(10) function in between each cell references and inserts a line break after each cell. Once you have the result, apply wrap text in the cells that have the results and you’ll get something as shown below:

Combine Cells in Excel - Address with linebreak

How to Combine Cells with Text and Numbers

You can also combine cells that contain different types of data. For example, you can combine cells that contain text and numbers.

Let’s have a look at a simple example first.

Suppose you have a dataset as shown below:

Combine Cells in Excel - text and numbers dataset1

The above data set has text data in one cell and a number is another cell. You can easily combine these two by using the below formula:

=A2&"-"&B2

Here I have used a dash as the separator.

Combine Cells in Excel - text and numbers dash

 

You can also add some text to it. So you can use the following formula to create a sentence:

=A2&" region has "&B2&" offices"

Here we have used a combination of cell reference and text to construct sentences.

Combine Cells in Excel - text and numbers dash sentence

Now let’s take this example forward and see what happens when you try and use numbers with some formatting applied to it.

Suppose you have a dataset as shown below, where we have sales values.

Combine Cells in Excel - text and numbers sales

Now let’s combine the cells in Column A and B to construct a sentence.

Here the formula I’ll be using:

=A2&" region generated sales of "&B2

Here is how the results look like:

Combine Cells in Excel - text and numbers sales 2

Do you see the problem here? Look closely at the format of the sales value in the result.

Combine Cells in Excel - text and numbers sales 3

You can see that the formatting of the sales value goes away and the result has the plain numeric value. This happens when we combine cells with numbers that have a formatting applied to it.

Here is how to fix this. Use the below formula:

=A2&" region generated sales of "&TEXT(B2,"$ ###,0.0")

In the above formula, instead of using B2, we have used the TEXT function. TEXT function makes the number show up in the specified format and as text. In the above case, the format is $ ###,0.0. This format tells Excel to show the number with a dollar sign, thousand-separator, and one decimal point.

Similarly, you can use the Text function to show in any format allowed in Excel.

Here is another example. There are names and date of birth, and if you try and combine these cells, you get something as shown below:

Combine Cells in Excel - DOB1

You can see that Excel completely screws up the date format. The reason is that date and time are stored as numbers in Excel, and when you combine cells that have numbers, as shown above, it shows the number value but doesn’t use the original format.

Here is the formula that will fix this:

=A2&" was born on "&TEXT(B2,"dd mmm yyy")

Combine Cells in Excel - DOB with text function

Again, here we have used the TEXT function and specified the format in which we want the Date of Birth to show up in the result.

Here is how this date format works:

  • dd – Shows the day number of the date. (try using ddd and see what happens).
  • mmm – shows the three letter code for a month.
  • yyy – shows year number.

Cool.. Isn’t it?

Let me know your thoughts in the comments section.

You May Also Like the Following Excel Tutorials:

  • akire says:

    Ok, I need help! =( How do I add space and commas using this formula: =H2&CHAR(10)&I2&CHAR(10)&J2&K2&L2
    everything lined up nicely, but it looks as if I concatenated some data here (city state and zip); See example below:
    Outcome:
    123 Happy life
    #21
    Los AngelesCA91111

    what I’m looking for:

    123 Happy Life
    #21
    Los Angeles, CA 91111

  • paradisio says:

    What if I want to get 0001@domain.com?
    When I combine 0001 and @domain.com, I get 1@domain.com even though the first cell is formatted at 0000 (4 digits)

  • indzara says:

    Thanks for the CHAR(10) tip. Useful.

  • Tim says:

    That’s very useful and I do like the way you explain the additional functions in an easy to understand way. Many thanks indeed.

  • Gopalakrishna Rao. says:

    Really you are helping the Excel lovers with your simple and very useful tricks/tips. Thanks on behalf of all my Excel lovers/addicts/friends/learners.

  • Hemang Patel says:

    Thanks a million Mr. Sumit Bansal for such a great function. I was wondering whether is it possible or not earlier but for now there is no doubt about it………….great help

  • >