The Excel CONCATENATE function combines texts/values from different cells into one cell.
Syntax
=CONCATENATE(Text 1, [Text 2], … )
| Argument | Argument description | |
|---|---|---|
| 1 | Text 1 | The first value to combine. |
| 2 | Text 2, Text 3, etc (Optional) | The other values to combine. |
Example: Simple Excel Concatenate formula
You will probably find yourself using the Concatenate function quite often to combine names. Here’s such an example.
Explanation
The Concatenate function in the Complete message column combines the values in the columns to its left. So by combining C2 (Hi there, ), B2 (Mr. ) and A2 (Charly) we end up with the text Hi there, Mr. Charly in the cell E2.
We do need to mention that the Title and Greeting column have an extra space after the values. If they wouldn’t have, the result would for example be Hi there,Mr.Charly (Note the lack of spaces).
Another way to add spaces in between words is by adding the space in the concatenate function itself, like so: =CONCATENATE(C2, " ", B2, " ", A2).
Concatenate alternative: the Ampersand & operator
There is an alternative to the concatenate function that is very convenient because of its shorter notation. The & operator.
You can use it by adding the & symbol between the two values that you want to combine. For example, you can substitute =CONCATENATE(A1, A2) by =A1&A2. See how much shorter that is?
Our spreadsheet editor does not currently support the & operator, so we cannot show an example here, but you can try it for yourself in Excel.
Note: Concatenate vs Concat
In Excel 2016 and later versions, the CONCATENATE function has been replaced with the CONCAT function. And except for the name, they both function exactly the same.
The Concatenate function is still available in these newer Excel versions for backwards compatibility, but it’s recommended to switch to Concat so your spreadsheets will keep working in future versions of Excel.
Questions
Yes, you can. You cannot just add a line break like a regular character, instead you have to use the CHAR() function inside of the Concatenate.
On Windows: add CHAR(10) to add a line break, on Mac: add CHAR(13).
So it would look something like =CONCATENATE(A1, CHAR(10), A2) (on Windows) or =CONCATENATE(A1, CHAR(13), A2)(on Mac).
Apart from the name, they are exactly the same. So they work in the same way. However, the Concat function is newer, and it’s recommended you use that in your sheets for future compatibility.
Yes, use the Flash Fill feature, the Text to Columns button or the MID, RIGHT & LEFT functions.