Whatever side of the Cloud vs. On Premise debate you take, it’s a fact that at this point there are two Excel 2016 versions out there, and I’m not talking about Professional, Home and so on… First there’s Excel 2016 with constant updates but mostly those updates are bug fixes and security patches. But then there’s Excel 2016 that comes as part of Office 365 and this one is now Excel 2016 and a half. At this point, those that are Office 365 subscribers get a better version of Excel or to be politically correct a newer version of Excel since this one also gets functionality updates and at the point that the next version of Excel will be available, those running Excel as part of Office 365 subscription will basically already have one! I’m not going to list all the differences between the two versions, you can get a more or less complete list of those here, I’ll only give you a scenario where two new functions in the 365 version in my humble opinion make a world of difference.
The scenario is simple. Let’s say we wish to join the words in cells A1 to A11…
If you know the Fill/Justify trick or VBA, you’re ok, but otherwise, this is a tedious process of endless clicking either by using the CONCATENATE function or writing a formula using the & symbol to join strings. It would look like this
=CONCATENATE(A1,” “,A2,” “,A3,” “,A4,” “,A5,” “,A6,” “,A7,” “,A8,” “,A9,” “,A10,” “,A11)
This is a function that we (Excel users) have been waiting for! Monumental upgrade of the CONCATENATE function because it not only combines the chosen strings, but allows you to select an entire area of values to combine and then let’s you choose, what should be the delimiter (so there’s no need to input the delimiter n times to join n+1 strings, you only need to give it once!) and whether empty cells are to be included.
The syntax is
and here’s TEXTJOIN applied to our scenario
And just to make the difference clearer, I used the FORMULATEXT and LEN functions in Excel
That’s 88 characters vs. 26… That’s less than one third of characters and less than one third of time… The main difference is derived from the ability to select the entire range instead of one cell at the time.
CONCAT function is less glamorous than TEXTJOIN, but it can still be considered a solid upgrade of the CONCATENATE function but with one big limitation.
Whereas it is great that you can select the whole area instead of picking out individual cells, but there there’s no argument that would let you define a delimiter, but it’s still nine clicks less than the CONCATENATE function.
First if anyone was wondering, if a file with these new functions was to be opened in a non-Office 365 Excel, those functions would translate into
would still yield results but could not be edited…
In my opinion these two function are very helpful and every data professional would benefit from using them and if you peeked at all the differences, you are more or less aware that there are two versions of Excel out there and that Microsoft has succeeded in making the version that comes as part of Office 365 subscription very appealing to those that intend to use Excel on any level.