Article 47 – New Functions in Excel 2016

This is a guest post contributed by Hannah Sharron of  http://spreadsheeto.com/

Microsoft has added six new built-in functions with the release of Excel 2016. These functions are also available in Office 365. In this article, we will take a quick look at three of those new functions.

TEXTJOIN

For a long time, the CONCATENATE has been a standard for many users as a method for joining data strings together. However, with the introduction of TEXTJOIN, Microsoft had refined this process even further.

First, let’s review CONCATENATE. This function basically takes arguments that are sequential string elements that the user desires to be joined together. Let’s look at a simple example of columns with separate first and last names as well as a column for a middle initial.

If we use the CONCATENATE function for both rows, we can achieve the goal of combining the values into a full name.

Note that we simply insert spaces between our cell references to create the final full name. However, if you look closely, you will notice that the data on our second row creates a double space between the first and last name because there is no data in the Middle column (cell B3).

This could be an issue if you have many rows of data and you wanted to quickly  copy this formula down. If you didn’t want extra spaces between names that did not have a middle name, this limits you from doing so.

But the new TEXTJOIN function provides a remedy to this problem. Let’s take a quick look at how to use this function with this same kind of data.

The syntax of TEXTJOIN requires a delimiter argument, an argument called ‘ignore_empty’, and text arguments (much like CONCATENATE). Also note that in place of TRUE/FALSE, 1/0 can be used. Also, default value for ignore_empty is TRUE or 1, so you can leave this blank also. If you leave it blank, TRUE will be used.

Things to note about this function in contrast to CONCATENATE are that you simply insert your delimiter (assuming it is the same between all your text elements) once. Also, that allows you to select a range of cell references rather than being limited to inserting them one at a time. Lastly, the ‘ignore_empty’ argument requires you to select either a TRUE or FALSE value. This allows you the flexibility to ignore an empty cell in a selected range of text values.

In our example, selecting TRUE for ‘ignore_empty’ allows us to copy the same formula down ensuring that there will always only be one space between names whether there is a middle name/initial value on that row or not.

TEXTJOIN also supports an array of delimiters. Till now, in this article, you have used only one delimiter. But you can use more than one.

=TEXTJOIN({",",":","-"},,A12:D12) will give

John,Steve:Natalie-Latia

=TEXTJOIN({",",":"},,A12:D12) will give

John,Steve:Natalie,Latia

TEXTJOIN is a wonderful function and can be used to accomplish many other kind of tasks apart from concatenation by its creative use.

a) Test whether a range contains only numbers

=ISNUMBER(–TEXTJOIN("",1,A15:E15))

Test whether a range contains only non numbers

Just reverse of a) i.e. if 1 gives FALSE, it contains non numbers also

b) Join only numbers

Array formula – =–TEXTJOIN("",1,IF(ISNUMBER(B1:H1),B1:H1,""))

c) Join only characters

Array formula =TEXTJOIN("",1,IF(ISTEXT(B1:H1),B1:H1,""))

d) Retrieve numbers from an Alphanumeric where E7:= 123ab45c6

Array formula – =–TEXTJOIN("",1,IFERROR(–(MID(E7,ROW(INDIRECT(LEN(E7)&":1")),1)),""))

e) Generate a unique list in a single cell

Array formula – =TEXTJOIN(", ",1,IF(MATCH(A1:A10,A1:A10,0)=ROW(A1:A10)-ROW(A1)+1,A1:A10,""))

Note – Array Formula is not entered by pressing ENTER after entering your formula but by pressing CTRL+SHIFT+ENTER. If you are copying and pasting this formula, take F2 after pasting and CTRL+SHIFT+ENTER. This will put { } brackets around the formula which you can see in Formula Bar. If you edit again, you will have to do CTRL+SHIFT+ENTER again. Don't put { } manually.

CONCAT

While TEXTJOIN offers a lot of flexibility as an alternative to CONCATENATE, the latter itself has undergone its own reincarnation. CONCAT is set to eventually replace CONCATENATE altogether but it will remain available in the built-in functions for now.

What CONCAT does that CONCATENATE cannot is that is allow the selection of ranges of cells rather than requiring each individual cell to be selected.

This has several implications. Look at what happens when using CONCAT with a vertical range of cells.

Aside from the flexibility of selecting ranges, you can also select multiple ranges separated by commas.

This concatenates the first range and then concatenates the second range to it.

CONCAT can also do many operations which TEXTJOIN can do. For example tricks a) to d) which were given for TEXTJOIN, you can do in CONCAT also. For example –

Concat only numbers

Array Formula =CONCAT(IF(ISNUMBER(H8:H14),H8:H14,""))

Concat only non numbers

Array Formula =CONCAT(IF(ISTEXT(H8:H14),H8:H14,""))

IFS

While the IF function is one of the simplest functions in Excel, it is also one of the most powerful. If you have ever created a nested IF, you know what this means. However, nested IF formulas can be a challenge to manage and troubleshoot at some point.

With the introduction of the IFS function, Microsoft has made life much easier for all you nested IF’ers out there. For the sake of comparison, look at the following example of a nested IF formula.

This is five levels of criteria by which we are testing a cell value (A2) to match the appropriate text string value. It achieves the following results.

Now look at the alternative using the IFS function.

What the IFS function allows is multiple logical tests and their resultant value if true. It is much more straightforward than nested IF formulas and less parentheses to keep track of.

For ELSE condition in IFS, last two arguments should be TRUE followed by the value which you need for ELSE conditon.

=IFS(A2>5,"A",A2>2,"B",TRUE,"C")

Hence, above will give the answer of C for any value less than 3.