I’ve been playing around with theTEXTJOINandCONCATfunctions this week.
These are both new functions in Excel 2016, introduced in the Office 365 subscription service.
Both of these functions let you join (concatenate) text in different cells together.
CONCAT simply mashes all values together without options.
What’s nice about both of these functions is that they can handle cell ranges.
But I’m also intrigued about how this might be useful inside other formulas.
For example, VBA has SPLIT and JOIN, and PHP has EXPLODE and IMPLODE, etc.
Here are a few ideas you might find interesting:
1.
Uppercase text
The example above will uppercase “apple” > “APPLE” in A1.
Strip non-numeric characters
Note: this is an array function - use control + shift + enter.
This example will strip all non-numeric characters in A1.
you’re free to do this same thing with the SUBSTITUTE function, but it’s more work.
If you’re curious, here is adetailed explanationof how this formula works.
More examples ofTEXTJOIN formulas.
