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.

Data set for Concatenate in Excel

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.


Selecting CONCAT as the concatenate function
  1. Select cell A2 as the first text string.

Now the formula bar will show;


Selecting the first text string for the concatenate function
  1. Open quotation marks and enter a hyphen between 2 spaces as the 2nd text string.

The updated formula is;

=CONCAT(A2,” – “

Entering a text string without a cell reference

Remember to enclose the text strings in formulas in quotation marks ⚠️

Kasper Langmann, Microsoft Office Specialist
  1. Give cell references to the cell in column b as the 3rd text string.

=CONCAT(A2,” – “,B2

Giving cell references for text strings for Excel concatenate function
  1. Close the parentheses and copy the concatenate formula to the below cells.
Use CONCAT function as the concatenate function to merge 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 😍

Excel concatenate function - Format cells with CHAR(10) - line feed

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

Combine texts from different cells using ampersand (&) sign

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.


Using Excel CONCATENATE function to combine text strings from multiple cells.

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.


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

You can see the following result.

Latest Excel concatenate function - TEXTJOIN

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 🎉

Concatenated range - Flash fill

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.

Ranges cannot be used with the ampersand sign to concatenate values into one column.

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


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.

Range arguments for Excel CONCATENATE function will spill results into two cells or more.

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

The word concatenate means to put things together as a connected series.

  1. Enter an equal sign and select CONCAT.
  2. Select the cell value in column 1 as the 1st text argument.
  3. Enter dash within quotation mark as the 2nd text string.
  4. Select the cell value in column 2 as the 3rd text argument.
  5. Close the parentheses and copy the formula to the below cells.

The most flexible concatenate function in Excel is TEXTJOIN.

You can specify, this concatenate function’s arguments as follows.

=TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)