Exploring 14 new Excel functions: Part 1

Microsoft recently unveiled not one, not two, but 14 new Excel functions.

Yes, 14.

Now before we continue, please note that these functions are far from generally available. In fact, at the time of this writing, these functions are currently available only to users running Beta Channel, Version 2203 (Build 15104.20004) or later on Windows and Version 16.60 (Build 22030400) or later on Mac.

In summary, the 14 new functions are essentially grouped as follows:

• Text manipulation.
• Combining arrays.
• Shaping arrays.
• Resizing arrays.

That's a lot to cover — too much for a single article at least. So to digest all this information in small bites, this article is presented in two parts. The first part covers the text manipulation and combining arrays groups, while the second part tackles shaping and resizing arrays.  

Let's start with text manipulation

Text manipulation functions

Unlike common text manipulation functions such as FIND, LEFT, LEN, MID, RIGHT, SEARCH, and SUBSTITUTE, the three new functions in this group allow you to dismember text strings without requiring a Ph.D. in astrophysics.

The three functions are:

1. TEXTBEFORE: Returns text that's before delimiting characters.
2. TEXTAFTER: Returns text that's after delimiting characters.
3. TEXTSPLIT: Splits text into rows or columns using delimiters.

Here's a peek at how they work.

The TEXTBEFORE function

The TEXTBEFORE function returns the string of text that occurs before a given substring (ie, a character or set of characters) in that string. It is the opposite of the TEXTAFTER function. TEXTBEFORE has the following syntax:

TEXTBEFORE(text, delimiter, [instance number], [ignore case])

The TEXTBEFORE function has the following arguments:

• text: This is required and represents the text string you are searching within. Wildcard characters are not allowed.
• delimiter: This is also required and represents the text in the text string that marks the point before which you wish to extract.
• instance number: This is the first optional argument and denotes the nth instance of the delimiter before which you wish to extract. By default, this is equal to one [1]. If a negative number is used here, the function starts searching for the delimiter from the end rather than the beginning.
• ignore case: This, too, is an optional argument and determines whether the search is case-sensitive or not. The default is FALSE, which means the search for the delimiter is case-sensitive; explicitly use TRUE to make the search case insensitive.

It should be further noted that:

• Excel should return an #N/A error if the delimiter is an empty string, but the current Beta version appears to return a blank.
• Excel returns a #VALUE! error if the instance number is zero (the default is one).
• Excel returns an #N/A error if the delimiter does not occur within the text.
• Excel returns an #N/A error if the instance number is greater than the number of occurrences of the delimiter within the text.

Please see the examples below:

The TEXTAFTER function

The TEXTAFTER function is exactly like TEXTBEFORE except that it returns the string of text that occurs after a given substring (ie, a character or set of characters) in that string. It has the following syntax:

TEXTAFTER(text, delimiter, [instance number], [ignore case])

The TEXTSPLIT function

The TEXTSPLIT function is intended to work like the Text to Columns button on the Data tab of the Ribbon, almost like the "inverse" of the TEXTJOIN function. It allows you to split a given text across rows or down columns. TEXTSPLIT has the following syntax:

TEXTSPLIT(text, [column delimiter], [row delimiter], [ignore empty], [pad with])

The TEXTSPLIT function has the following arguments:

• text: This is required and represents the text string you wish to split.
• column delimiter: This is optional and denotes one or more characters that specify where to spill the text across columns.
• row delimiter: This is optional and denotes one or more characters that specify where to spill the text down rows.
• ignore empty: Another optional argument, you should specify TRUE to create an empty cell when two delimiters are used. This argument defaults to FALSE, which means don't create an empty cell.
• pad with: Not to be confused with pad Thai, this final optional argument "pads" the resulting text range where cells would otherwise be blank. The default is N/A.

If there is more than one delimiter (row or column), then an array constant must be used. For example, to split by both a comma (,) and a period (full stop, .), use =TEXTSPLIT(text, {",", "."}).

Just for a change, some more examples:

Combining arrays

Since end users have been playing with arrays more and more, it has become noticeable that it can be quite challenging to combine data, especially when their sources are flexible in size. Two new functions may assist:

1. HSTACK: Combine dynamic arrays, stacking horizontally.
2. VSTACK: Combine dynamic arrays, stacking vertically.

The HSTACK function

The HSTACK function returns the array formed by appending each of the array arguments in a column-wise fashion (Microsoft's jargon, not ours). It has the following syntax:

HSTACK(array1, [array2, …])

The HSTACK function has the following argument(s):

• array: The first argument is required (others are optional) and represents the array(s) to append.

It should be noted that:

• HSTACK returns the array formed by appending each of the array arguments in a column-wise fashion. The resulting array will be the following dimensions:

• columns: The maximum of the column count from each of the array arguments.

• rows: The combined count of all the rows from each of the array arguments.

• Excel returns an #N/A error if an array has fewer rows or columns than the maximum in any selected array. To remove the errors, you should use the IFERROR function.

Please see the following examples:

The VSTACK function

The VSTACK function has the exact same syntax as HSTACK:

VSTACK(array1, [array2, …])

It should be noted that:

• VSTACK returns the array formed by appending each of the array arguments in a row-wise fashion. The resulting array will be the following dimensions:
   
  • rows: The maximum of the row count from each of the array arguments.
     
  • columns: The combined count of all the columns from each of the array arguments.
     
• Excel returns an #N/A error if an array has fewer rows or columns than the maximum in any selected array. To remove the errors, you should use the IFERROR function.

Some illustrations: 

Wow. That was a lot to go through. And we aren't even halfway through the functions. Don't sweat, though. You can explore the shaping and resizing arrays functions in Part 2.

— Liam Bastick, FCMA, CGMA, FCA, is director of SumProduct, a global consultancy specialising in Excel training. He is also an Excel MVP (as appointed by Microsoft) and author of Introduction to Financial Modelling. Send ideas for future Excel-related articles to him at liam.bastick@sumproduct.com. To comment on this article or to suggest an idea for another article, contact Jeff Drew at Jeff.Drew@aicpa-cima.com.