When working with Excel, you probably noticed that there are two methods of joining strings. One of them is by using the ampersand sign (&) and the other way is to use the CONCATENATE function.
The following formulas will return the same result.
1 |
="This "&"is "&"text" |
1 |
=CONCATENATE("This ","is ","text") |
Both will return “This is text”.
You can also create more arguments by joining spaces as separate arguments with CONCATENATE.
1 |
=CONCATENATE("This"," ","is"," ","text") |
You can also add spaces as separate words.
1 |
="This"&" "&"is"&" "&"text" |
TEXTJOIN function
A separator (in our case space) can add too much complexity to our formula. In Office 365 and Excel 2019, there is a new function called TEXTJOIN. It’s very similar to CONCATENATE, but instead of adding additional arguments as separators, you can add it only once, at the beginning of the formula.
Both formulas return the same result.
1 |
=CONCATENATE("This"," ","is"," ","text") |
1 |
=TEXTJOIN(" ",TRUE,"This","is","text") |
The usefulness of the TEXTJOIN function is more visible when you have more arguments, especially when these arguments can be referenced as a range.
1 |
=TEXTJOIN(" ",TRUE,A1:A10) |
Limitation of the CONCATENATE function
In the older Excel versions, the CONCATENATION function was limited to 30 arguments, but now it offers 255. I don’t think this is as important now as it used to be.
Joining text with ampersand doesn’t have this limitation.
Combining both for clearer formulas
Sometimes, using both ways together create a formula that is easier to read.
1 |
=CONCATENATE(FirstName," ",LastName) & CHAR(10) & CONCATENATE(Age, City, Occupation) |
Compatibility reasons
The last reason to have both ways is for historical and compatibility reasons. Older versions of Excel don’t support one method, and different spreadsheets like Apple Numbers, Google Docs, Open Office, or LibreOffice don’t support the other one.