Combine data in two or more cells to one cell in Excel

When working with Excel, your data is not always structured in accordance with your needs. Some of the times, you may want to  combine data from two or more columns into a single column.

Some of the examples is when you want to join names and addresses, first name and last name examples, combine dates and times in one column etc.

If that’s is one of your requirements, this article is for you.

Concatenate is a very useful function if you need to combine data into one cell.

For example, if you want to combine data in cell A1 with data in Cell B1, you will use the formula =CONCATENATE (A1,B1)

concatenate1

The above formula will produce the results below

concatenate2

Assuming the content in cell A1 is West and the content in B1 is Thompson, the above function will give you WestThompson. To separate the two names, you add custom value (space) in your formula to look like this

=CONCATENATE (A1,” “,B1)

concatenate3

 

The resulys will be as below

concatenate4

You can also combine more than one cell

concatenate6

Note that when you use a custom value in function, you have to add the opening and closing quotes

When you concatenate cells in Excel, you combine only the contents of those cells. In other words, concatenation in Excel is the process of joining two or more values together.

Apart from cells, you can concatenate text strings, cells, ranges, columns and rows

Note: there is no comma after the last parameter

  

Note

  • In Excel 2016, Excel 2019, Excel Online and Excel Mobile, CONCATENATE is replaced with the CONCAT function, which has exactly the same syntax.
  • Although the CONCATENATE function is kept for backward compatibility, it is recommended to use CONCAT instead because Microsoft does not give any promises that CONCATENATE will be available in future versions of Excel.
  • Excel CONCATENATE function requires at least one “text” argument to work.
  • In a single CONCATENATE formula, you can concatenate up to 255 strings, a total of 8,192 characters.
  • The result of the CONCATENATE function is always a text string, even when all of the source values are numbers.

Concatenate is different from merger cells

When you merge cells, you “physically” merge two or more cells into a single cell. As a result, you have one larger cell that is displayed across multiple rows and/or columns in your worksheet.

 

Using the “&” operator.

You can also use the “&” operator to combine or join data in a single cell

E.g. =A2&” “&B2

concatenate5

 

Leave a Reply

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