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