Concatenate

 

Use the CONCATENATE function in Excel to concatenate (join) strings. Instead of using CONCATENATE, simply use the & operator.

1. The CONCATENATE function below concatenates the string in cell A1 and the string in cell B1.

CONCATENATE function in Excel

2. Use the & operator to produce the exact same result.

& Operator in Excel

3. The CONCATENATE function below concatenates the string in cell A1, the string " and " (enclose text in double quotation marks) and the string in cell B1.

CONCATENATE function with Three Arguments

4. Use the & operator to produce the exact same result.

Join Strings in Excel

5. The CONCATENATE function below concatenates the string in cell A1, a space and the string in cell B1.

Concatenation

6. Use the & operator to produce the exact same result.

Insert Space

The CONCAT function in Excel 2016 or later produces the exact same result as the CONCATENATE function. Simply replace CONCATENATE with CONCAT in the formulas shown above.

7. The CONCAT function can also join a range of strings. If you don't need a delimiter (space, comma, dash, etc.) this can be useful.

CONCAT function in Excel

The TEXTJOIN function in Excel 2016 or later joins a range of strings using a delimiter (first argument).

8. First, take a look at all the extra spaces in column E below when you drag the CONCATENATE function in cell E2 down to cell E11.

CONCATENATE

9. The beauty of the TEXTJOIN function is that it can ignore empty cells (if the second argument is set to TRUE).

TEXTJOIN function in Excel

10. When using the CONCATENATE function, use the TEXT function to format numbers.

CONCATENATE and TEXT

Note: use 0 to display the nearest integer value. Use 0.0 for one decimal place. Use 0.00 for two decimal places, etc. Visit our page about the TEXT function for many more examples.