How to Concatenate in Excel: CONCAT, Ampersand, and more

Excel data is not always available in the format that we need when working with it.

There are situations when we want to combine the data from several cells into one cell.

But if you concatenate columns manually row-by-row, it can take hours ⏳

Luckily, the CONCAT function is here to the rescue.

And I’ll show you how to use it to combine data, step-by-step.

Also, I’ll show you how it holds up against the other awesome methods for combining cells 🏆

You can practice with me by downloading my sample workbook here.

How to concatenate with the CONCAT function

Assume that your manager has sent you the following data.

He asks you to combine data in product code and country of origin into one cell to create labels for products.

The following format should be used for the labels.

Product code – Country of origin

Let’s apply Excel concatenate function to create labels.

1. Enter an equals sign and select CONCAT in excel.

          =CONCAT(

2. Select cell A2 as the first text string.

Now the formula bar will show;

=CONCAT(A2,

3. Open quotation marks and enter a hyphen between 2 spaces as the 2nd text string.

The updated formula is;

=CONCAT(A2,” – “

4. Give cell references to the cell in column b as the 3rd text string.

=CONCAT(A2,” – “,B2

5. Close the parentheses and copy the concatenate formula to the below cells.

Pro Tip:

Assume that you want the format cells in the label column with wrap text.

In other words, you want the country of origin below the product code.

Then, use “Char(10)” instead of ” – “.

Char(10) is used to insert a line break.

Then, format cells in column c with wrap text.

Now, you can see all the data in the label column just like this 😍

If you want to learn more about starting a New Line in a Cell, click here.

Other concatenate methods

We can concatenate values in Excel using other concatenate functions and techniques.

Let’s explore! 👍

Concatenate with the Ampersand operator

The ampersand operator (&) in Excel another great way to concatenate text strings into one cell.

The ampersand calculation operator lets you combine data in multiple cells into one cell without a function.

Simply select concatenating values and insert an ampersand operator between each cell reference.

If you’re typing a text string without a cell reference, put it inside quotation marks.

You can enter the following formula in cell C2 and copy the formula to the below cells.

=A2&” – “&B2

It is a really simple formula 😊

Concatenate with the CONCATENATE function

If you are using old versions of Excel (below 2016), use the CONCATENATE function instead of CONCAT.

The CONCATENATE function’s arguments are the same as the CONCAT function’s arguments.

So, we can enter the CONCATENATE function as below.

=CONCATENATE(A2,” – “,B2)

Pro Tip:

The CONCATENATE function is available in current Excel versions for backward compatibility 🚧

If you are using newer Excel versions, try to use CONCAT.

The CONCATENATE function may not be available for Excel future versions.

Concatenate with TEXTJOIN

Microsoft Excel has recently introduced an advanced Excel function to combine text strings.

The function is TEXTJOIN.

The TEXTJOIN function helps to join text in ranges without giving individual cell references.

With this function, we can ignore empty cells and add a delimiter between each cell value 😲

You can type the below formula in cell C2.

=TEXTJOIN(” – “,TRUE,A2:B2)

Then double-click on the lower right corner of the cell.

You can see the following result.

Concatenate with Flash Fill

Flash fill handles combining text strings in different cells into one cell without a function in excel.

You have to just type the text strings as per the required format in the 1st cell.

So, you enter 5302 – Japan in cell C2.

Then press Control + E for flash fill.

The Excel worksheet is ready with concatenated values 🎉

Comparison of concatenating functions

Now you know different methods to combine text strings in Excel 👏🏻

Let’s compare them.

CONCAT vs Ampersand operator

Say you have to combine data from a range of cells.

Then, you can easily use CONCAT as it can select the entire cell range as one argument.

But, if you are using an ampersand, you have to select all cell references separately.

With ampersand, we cannot give cell ranges.

However, the CONCAT has the following limits.

• We can give a maximum of 253 text arguments
• The desired result must have a maximum of 32,767 characters

The above limitations do not apply when combining text strings with ampersand operators.

CONCAT vs TEXTJOIN

CONCAT and TEXTJOIN both use ranges.

But, the TEXTJOIN function has 2 main advantages over the CONCAT.

• We can specify a delimiter to be entered between cell references.
• We can set the function to ignore empty values.

So, the TEXTJOIN function is more flexible than CONCAT 👌

However, the TEXTJOIN function is currently available for Microsoft Excel 2019 and newer versions only.

CONCAT vs CONCATENATE

The function CONCAT is available from Excel 2019 version onwards. But the CONCATENATE function is available from Excel 2007 onwards.

In the CONCATENATE function, we cannot use a range of values as a text argument and get into one cell.

Then concatenated range will spill into multiple columns.

So, in CONCATENATE, text1, text2 and other text arguments must specify individually.

However, using CONCAT, we can give a cell range as a text argument.

That’s it – Now what?

That were lots of ways to combine text items in other cells into one text value 💪🏻

No more manual entries to combine text strings in Excel and no need to enter the same value over and over.

Joining cells and data works very well with other, more all-purpose functions like IF, SUMIF, and VLOOKUP.

Learn those functions in my free 30-minute online course. Enroll here.

Other resources

Want to dive deeper into joining cells?

Read our articles about the TEXTJOIN function, Merge cells, and Flash Fill.

If you want to separate text string in one cell into multiple cells, read our articles about How to Separate Names in Excel and Text-to-columns.

Frequently asked questions