How to concatenate strings in Excel

Although Excel tasks typically involve working with numbers, you can also manipulate text strings by concatenating them. In this guide, we're going to show you how to concatenate strings in Excel using the ampersand character and also with the help of other functions.

Download Workbook

Excel has 3 functions and 1 operator that can concatenate strings in your workbooks:

The ampersand operator (&) which simply combines the two strings before and after itself
CONCAT function can combine texts from multiple ranges
TEXTJOIN function can join strings using a specific delimiter
CONCATENATE function is the older version of the CONCAT function. However, it is still available for backwards compatibility.

Let's now see how you can concatenate strings in Excel by using these functions and the ampersand operator.

The Ampersand Operator (&)

The Ampersand Operator (&) is one of the simplest ways to combine two strings, as it's typically easier to type in a single operator than a function. On the other hand, the functionality of this approach is limited. You need to add more characters for each new string after the second.

Example

="A"&"B" return AB

CONCAT Function

The CONCAT function combines each string in its arguments, but note that it doesn't provide any delimiter support. Thus, it suitable for combining raw values as they appear in your workbook.

Syntax

CONCAT(text1, [text2], …)

Example

=CONCAT(B3:D3)

Excel introduced the CONCAT function in the 2016 version. So, you need to have Excel 2016 or newer. Also, the function is available for Office 365, Excel Mobile, and Excel for the web. Here is a link for Microsoft's article.

TEXTJOIN Function

The TEXTJOIN function combines strings in a given range. The difference between the TEXTJOIN and the CONCAT functions is the TEXTJOIN function's ability to add delimiters between each string in the array. Thanks to the delimiter support, you can add a delimiter string and a logical value to determine whether the function is to ignore empty cells. If the function is set to not ignore empty cells and there is an empty cell in the range, the delimiter string will be repeated in the resulting string.

Syntax

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

Example

=TEXTJOIN(delimiter,ignore_empty,text)

The TEXTJOIN function was first introduced in the Excel 2016 version. So, you need to have Excel 2016 or newer. Also, the function is available for Office 365, Excel Mobile, and Excel for the web.

CONCATENATE Function

The CONCATENATE function is the oldest text join function of Excel, and the function has limited capabilities - The CONCATENATE function doesn't support ranges. Thus, you need to enter each cell or string as individual arguments. This behavior makes the function a bit harder to use in some cases. In the end, Microsoft has released the CONCAT function as a replacement for the old CONCATENATE. If you have Excel 2016 or further version, we recommend not using this function.