Concatenate Strings

 

& Operator | Concatenate | Concat | TextJoin | More about Concatenation

To concatenate (join) strings in Excel, use the & operator, the CONCATENATE function, the CONCAT function or the TEXTJOIN function.

& Operator

Simply use the & operator to concatenate (join) strings in Excel.

1. For example, the formula below concatenates the string in cell A2 and the string in cell B2.

& Operator in Excel

2. To insert a space, use double quotation marks with a space between them.

Concatenate Strings in Excel

3. The formula below concatenates the string in cell B2, a comma and a space and the string in cell A2.

Insert Comma and Space

Concatenate

Instead of using the & operator, use the CONCATENATE function in Excel.

1. The CONCATENATE function below concatenates the string in cell A2 and the string in cell B2.

Concatenate Function in Excel

2. The CONCATENATE function below concatenates the string in cell A2, a space and the string in cell B2.

Concatenate Function with Three Arguments

3. The CONCATENATE function below concatenates the string in cell B2, a comma and a space and the string in cell A2.

Concatenation

Concat

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

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

TextJoin

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

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

Without TextJoin Function

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

TextJoin Function in Excel

More about Concatenation

When joining text and a number, use the TEXT function to format that number.

1. Without using the TEXT function this would be the result.

Without Text Function

2. With the TEXT function.

Text Function in Excel

Note: #,## is used to add comma's to large numbers. 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.

3. When concatenating strings, use CHAR(10) to insert a line break. For example, the address below contains two line breaks.

Concatenation with Line Breaks

Note: don't forget to enable text wrapping.