When you need to concatenate text strings together in Excel, there are a few ways of doing it. There’s the old standby CONCATENATE function, there’s the ampersand (&) in the formula method, and with Excel 2016 there’s now the CONCAT function and TEXTJOIN function.
Here’s a summary of the ways to concatenate text strings. I’ll use the same text strings for each method to show the difference.
CONCATENATE Function
Excel’s standard CONCATENATE function looks like this:
CONCATENATE(text1, [text2], …)
- text1 = the first string to join. It’s required
- [text2] = the second string to join to the first. It’s optional and there’s a maximum of 255 strings, or 8,192 characters
Here’s how the formula would look:
=CONCATENATE(“Hello world.”, ” “, “Today is”, ” “, “Monday.”, ” “, “There”, ” “, “are”, ” “, “no”, ” “, “appointments”, ” “, “in your calendar.”)
Here’s the result of the formula:
“Hello world. Today is Monday. There are no appointments in your calendar.”
The Ampersand Method
The ampersand (&) method is what I would use instead of the CONCATENATE function. I found it quicker to type either the couple of strings or cell references with the ampersand (&) between rather than typing out CONCATENATE.
It’s probably just a personal preference though. With the new functions added to Excel 2016, I just may change my method.
Here’s how the formula would look with the ampersand (&) method:
=”Hello world.” & ” ” & “Today is” & ” ” & “Monday.” & ” ” & “There” & ” ” & “are” & ” ” & “no” & ” ” & “appointments” & ” “& “in your calendar.”
Here’s the result of the formula:
“Hello world. Today is Monday. There are no appointments in your calendar.”
So, the same result as the CONCATENATE function, but looking at the two, I actually find this method of joining text harder to read the formula.
CONCAT Function
CONCAT is one of the two new Excel 2016 functions that you’ll see here. It has replaced CONCATENATE, though CONCATENATE is still available for backwards compatibility.
The CONCAT function looks like this:
CONCAT(text1, [text2], …)
- text1 = the first string to join. It’s required
- [text2] = the second string to join to the first. It’s optional and there’s a maximum of 253 strings, or 32,767 characters
Here’s how the formula would look:
=CONCAT(“Hello world. “, “Today is”, ” “, “Monday.”, ” “, “There”, ” “, “are”, ” “, “no”, ” “, “appointments”, ” “, “in your calendar.”)
Here’s the result of the formula:
“Hello world. Today is Monday. There are no appointments in your calendar.”
TEXTJOIN Function
TEXTJOIN is the other new text function added to Excel 2016. Like CONCATENATE and the new CONCAT, it combines text. However, it also includes the ability to add a text delimiter and some logic to the joining of text.
The TEXTJOIN function looks like this:
TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
- delimiter = a text string in double quotes
- ignore_empty = TRUE or FALSE. If the function should ignore empty values
- text1 = the first string to join. It’s required
- [text2] = the second string to join to the first. It’s optional and there’s a maximum of 252 strings, or 32,767 characters
=TEXTJOIN(“”,TRUE,”Hello world.”, ” “, “Today is”, ” “, “Monday.”, ” “, “There”, ” “, “are”, ” “, “no”, ” “, “appointments”, ” “, “in your calendar.”)
Here’s the result of the formula:
“Hello world. Today is Monday. There are no appointments in your calendar.”
Notice, though, that the same empty characters are between the text. That’s not using the full power of the TEXTJOIN function.
If you remove the space strings between the words, and instead use the TEXTJOIN function’s ability to insert the space strings, the formula would look like this:
=TEXTJOIN(” “,TRUE,”Hello world.”, “Today is”, “Monday.”, “There”, “are”, “no”, “appointments”, “in your calendar.”)
Here’s the result of the formula:
“Hello world. Today is Monday. There are no appointments in your calendar.”
The result is the same, but the formula is now shorter.
This new TEXTJOIN function now allows you flexibility in how to use and manipulate text strings. You can specify what you want between each string, and the formula is shorter, and easier to read.
Conclusion
The new functions of CONCAT and TEXTJOIN that have been added to Excel 2016 have greatly increased the amount of characters that can be concatenated.
As well, the TEXTJOIN function has increased the flexibility available to concatenate text strings in Excel. This should give you more freedom and power to quickly concatenate text strings.
Let me know what you think about the new text functions in Excel 2016.