In this tutorial, I tell you about a brand new function in Excel 2016 for Windows that will make your life easier when combining data from a range of cells. Before reading on, this new function is only available in Excel Online and Excel 2016 for Windows that has been installed as part of Office 365. In other words, if you purchased a single-user copy of Excel, you will not have this function available to you.
In the screenshot below, in column E I want to combine, for each row, the contents of columns A-D, with a single space between each item.
I can do this in a couple of ways:
- =A2 & ” ” & B2 & ” ” & C2 & ” ” & D2
- =CONCATENATE(A2,” “,B2,” “,C2,” “,D2)
In Excel 2016 for Windows, there’s now another way – using the new TEXTJOIN function, which allows you to specify a range (rather than the individual cells) and a character (or characters) to use as the separator. The second argument (TRUE/FALSE) indicates whether to ignore blank cells in the range (TRUE).
=TEXTJOIN(” “,TRUE,A2:D2)
This mean combine together the contents of the range A2:D2, putting a single space between each cell value.
The formula in F2 above is =TEXTJOIN(“, “,TRUE,A2:D2).
This combines the contents of A2:D2, putting a comma and a space between each cell value.
IMPORTANT NOTE
If you open a file containing this function using a version of Excel that does not support it, the cell will contain the correct result of the function, however the formula bar will display =_xlfn.TEXTJOIN(” “,TRUE,A2:D2) and any changes to the values in the referenced range will result in the cell containing the formula displaying #NAME
Download the sample file here
Hi Mike
Textjoin looks like a handy little function that will certainly be quicker and easier to use that Concatenate, where multiple cells are involved.
I’m a little curious as to why it is available in free software (Excel Online) but not available within a purchased, single-user copy of Excel 2016.
I recall there were similar scenarios in previous versions where you could create forms (surveys) in Excel Online – but not in Excel 2013.
Wondered if yourself, or any of your subscribers, have any explanation as to the logic of why free software contains functions not available to paying customers?
Thanks for sharing the Textjoin function and I look forward to hearing more of your insights in Excel 2016.
Gill
Hi Gill. I found another use for TEXJOIN last week. A customer had a long list of email addresses and needed to copy and paste as a single string into a mail program with a semi-colon between. A lot simpler to be able to specify the range and a single instance of the semi-colon.
As for your other point, it’s more a case that Microsoft have added the functionality to Excel Online. You’re correct in that if you sign up for a free MS account you get access to the online (browser-based) versions of Word, Excel and PowerPoint but you also get access to Office Online as part of an Office 365 subscription – and I think that this is the reason Microsoft have done it – to encourage people to signup for a 365 subscription by giving them first access to the new features.
I’m sure the new features will make their way into the single-copy versions via a service pack in due course.
Thanks again for your comment and question.
Hi Mike
One of my clients had the reverse question this week. They received an email with 70+ email addresses in the cc line and tried to copy and paste them into a spreadsheet. All the entries ended up in one cell, with comma’s between them – but were easily separated when I brought the ‘Text to Columns’ tool to their attention. It’s amazing how much time and effort can be saved if users learn to use the tools available in Excel.