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).
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.
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