​​​​Concatenate

The Excel CONCATENATE function combines texts/values from different cells into one cell.

Syntax

=CONCATENATE(Text 1, [Text 2], … )

Argument Argument description
1Text 1The first value to combine.
2Text 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.

Spreadsheet editor

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

Can I add a line break/carriage return inside of Concatenate?

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).

What is the difference between Concatenate and Concat?

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.

Is there a way to split a cell’s contents instead of combining them?

Yes, use the Flash Fill feature, the Text to Columns button or the MID, RIGHT & LEFT functions.

​Get Good at Excel on Autopilot

Sign up to our newsletter and receive ​Excel articles, tips and tricks delivered straight to your inbox. All you have to do is read them! ​Plus, to get you started, you'll receive a PDF with 200+ Excel shortcuts.

>