Table of Contents

CONCATENATE

Use CONCATENATE CONCAT TEXTJOIN or the & operator in Excel to concatenate (join) two or more text strings.

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.

11. Use CHAR(10) to insert a line break.

Concatenation with Line Breaks

Note: don’t forget to enable text wrapping. On the Home tab in the Alignment group click Wrap Text.

12. Let’s take a look at one more cool example that uses the TEXTJOIN function. First the IF function below finds all members of Team 1.

IF function

Explanation: the IF function checks each cell in the named range Teams. If equal to the value in cell E3 it returns the name from the named range Names else it returns an empty string (two double quotes with nothing in between).

13. The IF function returns an array constant stored in Excel’s memory. Select the formula in the formula bar and press F9 to see this array constant.

Array Constant

14. Add the powerful TEXTJOIN function to concatenate these names. Use a comma and space as the delimiter (first argument) and set the second argument to TRUE to ignore the empty strings. Finish an array formula by pressing CTRL + SHIFT + ENTER.

Advanced TEXTJOIN formula

Note: Excel adds the curly braces {}. In Excel 365 or Excel 2021 finish by simply pressing Enter. You won’t see curly braces. Mia James and Sophia are in Team 1. Double click the lower right corner of cell F3 to quickly copy this formula to the other cells.

Leave A Comment

Excel meets AI – Boost your productivity like never before!

At Formulas HQ, we’ve harnessed the brilliance of AI to turbocharge your Spreadsheet mastery. Say goodbye to the days of grappling with complex formulas, VBA code, and scripts. We’re here to make your work smarter, not harder.

Related Articles

The Latest on Formulas HQ Blog