Concatenation is a function that combines the data of two or more cells into a single cell. Very often, the data given to you might not be organised as per your requirement. This makes it very difficult to use the data to your benefit. To make the set more useful and effective, concatenate excel function can be of great help. 

In this article, our focus will be to understand what exactly concatenation is, how is it different from merging cells and what are the different ways to use concatenate function in excel.

What is Concatenation and Concatenate Formula?

Before we get into the concatenate excel formula, let’s define concatenate and what exactly does Concatenation mean?
The exact meaning of the word ‘Concatenation’ is to link things together or to combine them. 

When it comes to Excel, the concatenate formula or the ‘Concat Formula’ as it is often referred to as can mean combining 2 or more cells, columns or text strings. 

Now, let’s move on to the different ways of combining data using the concat function in excel.

How to use the Concatenate Excel Formula to combine data in 2 cells?

Consider the following data with First Names in “Column A” and Last Name s in “Column B”. Say you want to combine the data in cells A2 and B2 so that the first name and the last name of the person, appear together in cell C2. 

Now, there are 2 ways to combine the data in cells A2 and B2. These are the basic ways to concatenate cells in excel. 

Alternative 1- Without any space or separator 

To concatenate two cells in excel without any space or comma,  follow these steps: 

1: Go to cell C2. Type =CONCATENATE(A2, B2)

2: Hit enter. Cell C2 will show the result as JackSmith without any space. 

Alternative 2- With space

To concatenate with space in excel, follow these steps: 

1: Go to cell C2. Type =CONCATENATE(A2, “ ”, B2)

2: Hit enter. Cell C2 will show the result as Jack Smith with a space 

Alternative 3- With a comma

To concatenate excel with comma i.e., to add a comma between the data of 2 cells, follow these steps: 

1: Go to cell C2. Type =CONCATENATE(A2, “, ”, B2)

2: Hit enter. Cell C2 will show the result as Jack, Smith with a comma and space.

You can also use the same steps to concatenate cells with any other punctuation or character by replacing it with the comma in the above example. 

Excel Concatenate Strings

In addition to combining cell values, you can also add a string to the combination result by using the concatenate excel function. Let’s say you want to add the word ‘Alan’ as a string to the same data set. To concat strings in excel, follow these steps: 

1: Go to cell C2. Type =CONCATENATE(A2, “ Alan ”, B2)

2: Hit enter. Cell C2 will show the result as Jack Alan Smith

Similarly, you can add more strings while combining 2 or more cells. 

& Operator In Excel

Another way to join text in Excel is through the ampersand sign (&). So if you feel that typing ‘CONCATENATE’ is too much work, this makes your job a lot simpler. 

To use the & Operator, follow these steps: 

1: Go to cell C2. Type =A2&B2

2: Hit enter. Cell C2 will show the result as JackSmith

Similar to the Concatenate function, you can add space or special characters in the combined cell by using functions =A2&” “&B2 or =A2&”,”B2

How to concatenate columns in excel?

It is entirely possible to concatenate entire columns in excel and also very simple. You can do it by adding the formula to one cell and then copying it to the whole column by dragging the fill handle. 

Important things to note 

1. All versions of Excel 365- 2007 support the excel concatenate function.
2. Concat Function is also available in Excel 365- 2019, which is a contemporary successor of the concatenate function but carries the exact same syntax. 
3. The concatenate function in Excel can combine up to 30 text items but each of them has to be listed individually. It does not take into account a range. For example, =CONCATENATE(A2:D2) is not a valid function but =CONCATENATE(A2, B2, C2, D2) is valid. 
4. The CONCAT formula, on the other hand, will accept the range command for versions Excel 2019 and higher.
5. In case the function you have entered is invalid, Microsoft Excel will show a #VALUE! Error in the Excel string concatenation function.
6. The concatenate excel formula has a limit of 255 strings while the & operator has no such limit. 
   
   

Other important formulas 

If you enjoyed learning about the concatenate formula in excel, here are some more formulas that you might enjoy learning about: 

• 3 Quick steps to merge cells in excel
• Remove normal, leading and trailing spaces in excel
  
  

Conclusion

These are all the different ways to concatenate cells In excel.

We hope that we were able to help you clear your understanding of Concatenation and the concatenate function in excel.

If you still have any questions or doubts, or in case you want to share your feedback or suggestions, feel free to drop them in the comments section and our team will reach out to you very soon! We’d really appreciate it.