The term ’concatenate’ simply means linking or joining things together. In Microsoft Excel, CONCATENATE or CONCAT function is used for joining two or more cells/columns’ data together.

There are two methods to combine the data in Excel:

  • Using CONCATENATE/CONCAT function 
  • Using ‘&’ operator

In this article, we will show you how to combine multiple cells into a single string using the Concatenate function in Excel.

Combining Cells using the CONCATENATE/CONCAT function

The CONCATENATE function is one of the Excel Text Functions which helps you to combine two or more cells into one string, whether they contain numbers, dates, or text strings.

From Excel 2016 onwards, Excel replaced ‘CONCATENATE’ with the ‘CONCAT’ function. That means, in later versions of Excel, you can use either ‘CONCATENATE’ or ‘CONCAT’, but in older versions of Excel (2013 and below), you can only use the ‘CONCATENATE’ function.

Syntax

The syntax for the CONCAT function in Excel is:

=CONCAT(text1, text2, ... text_n)

For Microsoft Excel 2013 and older version, the syntax is:

=CONCATENATE(text1, text2, ... text_n)

Arguments

text1, text2, … text_n – The values you want to join together, these values can either be strings, cells, or ranges of cells.

Concatenate Text Strings

You can join two or more text strings into a single string with CONCAT function.

To concatenate, first, select the cell where you want the result, and enter the formula. If you are directly using Text string as arguments in the function, be sure to enclose them in double quotation marks (“”) as shown below.

Concatenate Cell Values

The CONCAT formula to concatenate cells A1 and B1 values is:

=CONCAT(A1,A2)

Add cell references as arguments in the formula to join the cell values.

Concatenate Two Cell Values with a Separator

To separate the values with a space, enter ” ” in between the cell references.

=CONCAT(A1," ",B1)

Enter space (” “) enclosed in double quotation marks in the second argument as shown below.

Concatenate Cells with Special Characters

You can also concatenate values with various delimiters such as commas, spaces, various punctuation marks, or other characters such as a hyphen or slash.

To combine two cells with a comma:

=CONCAT(A1,",",B1)

When you enter delimiter (,) make sure to enclose them in double quotation marks.

Concatenate a Text string and Cell Values

The CONCAT function below joins the string in cell A1, the string ‘and’, and the string in cell B1.

=CONCAT(A1," and ", B1)

We added a space before and after the word ” and “ in the second argument of the formula to separate the concatenated strings and also to add meaning to the text string.

You can add a text string in any argument of your CONCAT/CONCATENATE formula.

Concatenate Columns in Excel

Suppose you have a list of First names and Last names in two separate columns and you want to join them to make one column of Full names. To concatenate two or more columns, type a concatenation formula in the first cell and then apply it to the entire column by dragging the fill handle.

To copy the formula to other cells, just drag the small square (fill handle) in the lower right-hand corner of the selected cell.

Now, you have a column of full names.

Concatenate a Range of Strings

You can also join a range of strings using the CONCAT function. If you don’t want to add a delimiter between the string (space, comma, dash, etc.), then this formula can be useful:

=CONCAT(A1:F1)

If you want to join a range of strings with a delimiter (” “), use the formula below:

=CONCAT(A2," ",B2," ",C2," ",D2," ",E2)

Concatenate a Range of Strings using the TEXTJOIN function

The TEXTJOIN function is also another function you can use to join a range of cell data. The TEXTJOIN function concatenates (combines) the values from multiple ranges and/or strings with a given delimiter. Unlike the CONCAT function, TEXTJOIN enables you to set whether to ignore empty values or not.

=TEXTJOIN(" ",TRUE,A2:E2)

This formula joins a range of strings with a delimiter (which you specify in the first argument) between each value. This formula ignores empty cells because its second argument is set to ‘TRUE’.

You can only use the TEXTJOIN function in Excel 2016 or later version.

Concatenate using ‘&’ Operator

The ‘&’ Operator is another way to combine text strings and cells in Microsoft Excel. The ampersand operator (&) is actually an alternative to CONCATENATE function.

The ampersand operator (&) formulas are short, simple and easy to use.

Syntax

=cell_1&cell_2

Use the & operator to combine the values of cells A1 and B1:

=A1&B1

Select a cell where you want the result and type the above formula.

Concatenate Two Cell Values with a Separator using ‘&’ Operator

To concatenates the values in cell A1 and cell B1, and a space in between using the ‘&’ operator:

=A1&" "&B1

Another example with another delimiter:

Concatenate a Text string and Cell Values using ‘&’ Operator

You can also use the ‘&’ operator to join the string in cell A1, the text ‘and’ in between, and the string in cell B1.

=A1&" and "&B1

We added a space before and after the word ” and “ to separate the concatenated text strings. Always enclose the text in double quotation marks in Excel Formula.

CONCAT vs ‘&’ operator

The only real difference between CONCAT and “&” operators is that the Excel CONCAT function has 255 strings limit and no such limitations for the ampersand.

That’s how you concatenate strings in Excel.