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)
The above formula will produce the results below
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)
The resulys will be as below
You can also combine more than one cell
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
- 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