Announcing New Text and Array Functions

[JoeMcDaid]

September 29th 2022 Update
These functions are now fully deployed to Excel for the Web and users of Office 365 on the Current Channel.

I’m thrilled to share with you the availability of 14 new Excel functions designed to help you more easily manipulate text and arrays in your worksheets. 

Text Manipulation Functions

When working with text, a common task to complete is “break apart” text strings using a delimiter. You can already do this with combinations of SEARCH, FIND, LEFT, RIGHT, MID, SUBSTITUTE, and SEQUENCE, but we’ve heard from many of you that these can be challenging to use.

To make it easier to extract the text from the start or end of a cell’s contents, we are releasing two functions that simply return everything before or after your selected delimiter. Welcome, TEXTBEFORE and TEXTAFTER!

We’ve also made it easy to “split” text into multiple segments using TEXTSPLIT. Each text segment is then automatically spilled into its own cell through the magic of dynamic arrays. 

 

• TEXTBEFORE - Returns text that’s before delimiting characters

• TEXTAFTER - Returns text that’s after delimiting characters

• TEXTSPLIT - Splits text into rows or columns using delimiters

Array Manipulation Functions

Since the release of dynamic arrays in 2019, we’ve seen a large increase in the usage of array formulas. To make it easier to build compelling spreadsheets using dynamic arrays, we are releasing a collection of 11 new array manipulation functions.  

Combining Arrays

It can be challenging to combine data, especially when their sources are flexible in size. With VSTACK and HSTACK, you can easily combine dynamic arrays, stacking your data vertically or horizontally. 

• VSTACK - Stacks arrays vertically
• HSTACK- Stacks arrays horizontally

 

Shaping Arrays

It has been challenging to change the “shape” of data in Excel, especially from arrays to lists and vice versa. If you find yourself with a two-dimensional array that you would like to convert to a simple list, use TOROW and TOCOL to convert a 2D array into a single row or column of data. 

Using the WRAPROWS and WRAPCOLS functions, do the opposite: create a 2D array of a specified width or height by “wrapping” data to the next line (just like the text in this document) once your chosen width/height limit is reached. 

 

• TOROW - Returns the array as one row
• TOCOL - Returns the array as one column
• WRAPROWS - Wraps a row array into a 2D array
• WRAPCOLS - Wraps a column array into a 2D array

Resizing Arrays

Arrays too large? No problem. Enter the TAKE and DROP functions! They enable you to reduce your arrays by specifying the number of rows to keep or remove from the start or end of your array.

Similarly, using CHOOSEROWS or CHOOSECOLS, you can pick specific rows or columns out of an array by their index.

EXPAND allows you to grow an array to the size of your choice—you just need to provide the new dimensions and a value to fill the extra space with. 

 

• TAKE - Returns rows or columns from array start or end
• DROP - Drops rows or columns from array start or end
• CHOOSEROWS - Returns the specified rows from an array
• CHOOSECOLS - Returns the specified columns from an array
• EXPAND - Expands an array to the specified dimensions

 

Scenarios to try

• Use “ “ (space) as a delimiter with TEXTBEFORE to extract the first name and TEXTAFTER to extract the last name 
• Use TEXTSPLIT to separate the names into an array with “ “ (space) as a delimiter 

When you want to combine two ranges of data: 

• Use VSTACK to combine two ranges of data vertically 
• Use HSTACK to combine two ranges horizontally 

 

Availability 

These functions are currently available to users running Beta Channel, Version 2203 (Build 15104.20004) or later on Windows and Version 16.60 (Build 22030400) or later on Mac.

Don’t have it yet? It’s probably us, not you.

Features are released over some time to ensure things are working smoothly. We highlight features that you may not have because they’re slowly releasing to larger numbers of Insiders. Sometimes we remove elements to further improve them based on your feedback. Though this is rare, we also reserve the option to pull a feature entirely out of the product, even if you, as an Insider, have had the opportunity to try it.

Feedback

If you have any feedback or suggestions, you can submit them by clicking  Help > Feedback. You can also submit new ideas or vote for other ideas via Microsoft Feedback.

Want to know more about Excel? See What's new in Excel and subscribe to our Excel Blog to get the latest updates. Stay connected with us and other Excel fans around the world – join our Excel Community and follow us on Twitter.

Joe McDaid (@jjmcdaid)
Program Manager, Excel

[markFitzpatrick]

Cheers, @Sergei Baklan - OK, now it makes more sense, but not what I was expecting. I thought 

=TEXTSPLIT(C4,{",",";"},,0)

would be written as:

=TEXTSPLIT(C4,",",";",0)

and would result in:

which begs the question, what would Ignore_Empty = TRUE look like?

But it appears when Ingore_Empty = TRUE, there is no distinction between Row_Delimiter and Col_Delimiter.  Did your flat (single row) result from setting Ignore_Empty=TRUE or from packing an array {",",";"} into Col_Delimiter?

NB: I think the arguments for TEXTSPLIT don't fit standard Excel structure, which is usually R, C (e.g., INDEX, SEQUENCE, RANDARRAY, MAKEARRAY...).

[Sergei Baklan]

@markFitzpatrick , with array of delimiters we split text into the row (or column, depends on on which place it is). TRUE/FLASE defines will we add empty cells between sequential delimiters used in an array or not. Thus if we have {",", ";" } as the parameter we insert or not empty cell if within the text we have both "," and ";".

Your second sample says that we split on columns everything delimited by comma and on rows delimited by semicolon. TRUE/FALSE works here as well if comma and semicolon appear sequentially, the difference will be like this

Since we have both after the "x'"  it could be inserted empty cell or not, depends on parameter.

[markFitzpatrick]

Thanks @Sergei Baklan. It makes more sense now. But perhaps Example 2 (i.e., TEXTSPLIT(C4,",",";",0) ) to result in:

based on C4 having: a,b,c;x,;y

[Sergei Baklan]

@markFitzpatrick - yes, exactly

[Ilgar_Zarbaliyev]

Thank you, Excel Team!

I liked the following functions more than others:

TextSplit,

Take,

Drop

[Sergei Baklan]

@Ilgar_Zarbaliyev , depends on goals

[Ilgar_Zarbaliyev]

@Sergei Baklan Possible.

[ShaRK55]

Great stuff Excel Team!

[BrentE]

Will the TEXTSPLIT functions respect text as not-dates?  If I split "1-5,10-15" or "2/3, 3/5" around the comma, will the new commands return "1-5" and "10-15" or Jan 5 and Oct 1 2015?

[Peter Bartholomew]

@BrentE I can confirm that the formula returns text and not dates, similar to the reverse operation

= TEXTJOIN("-",,{1,5})

[kal_at_kalx_net]

I have made some add-ins available at https://xlladdins.com/. The xll_array add-in only works for numerical ranges. It uses the FP data type behind the scenes for high-performance numerical computing. The xll_regex add-in is quite basic and not easy to use unless you know regular expressions. The intended audience is expert Excel users and only works for Excel on Windows.

The add-ins and installer are signed, but you will have to jump through the usual security hoops the first time around. It uses ClickOnce so you will be notified of updates as they become available. Full source at https://github.com/xlladdins/xlladdin.

[Patrick Matthews]

I've been waiting so long for TEXTSPLIT. No more UDFs to handle that task

I'm probably more excited about VSTACK and HSTACK, though. The ability to combine multiple arrays into one big array will be fabulous!

[Baccma]

@Patrick Matthews  - you could do a VSTACK and HSTACK at present with a lambda function. Agreed though that it makes it a  lot easier.   This is what I was using for vertical stacking:

=LAMBDA(Array1,Array2,Defaults, LET(R,ROWS(Array1)+ROWS(Array2), C,MAX(COLUMNS(Array1),COLUMNS(Array2)), D,IF(COLUMNS(Defaults)>1, INDEX(Defaults,1,0), LEFT(INDEX(Defaults,1,1), SEQUENCE(1,C, LEN(INDEX(Defaults,1,1)),0))), X,SEQUENCE(1,C), Y,SEQUENCE(R), IF(Y<ROWS(Array1)+1, IFERROR(INDEX(Array1,Y,X), IFERROR(INDEX(D,1,X),"")), IFERROR(INDEX(Array2,Y-ROWS(Array1),X), IFERROR(INDEX(D,1,X),"")))))

not, admittedly easy to audit.  So the new functions are indeed very welcome.

[Sameer_Bhide]

@JoeMcDaid 

Does EXPAND work with an Array of Arrays - So would it Expand the Array returned by XLOOKUP and return a rectangular array of 3 x 3

=EXPAND(XLOOKUP,(E2:E4,A1:A10,B1:D10),3,3)

Cheers

Sam

[Sergei Baklan]

IMHO, nothing works with array of arrays, at least directly.

[Sameer_Bhide]

@Sergei

"...nothing works with array of arrays..."

There are some things that do For example 

=XLOOKUP,(E2:E4,A1:A10,B1:D10)*{1,1,1} will return a 3 X 3 Array provided B1:D10 has numbers - The problem is what if one of the columns is Text ? .

Then you need to use INDEX to return the Array of Arrays as below

=INDEX(XLOOKUP,(E2:E4,A1:A10,B1:D10),SEQUENCE(3),SEQUENCE(,3,1,0))

But what's the point of XLOOKUP because INDEX/XMATCH or INDEX/MATCH would work just fine as below

=INDEX(B1:D10,XMATCH(E2:E4,A1:A10),SEQUENCE(,3))

I was hoping the EXPAND/XLOOKUP Combo would do the trick as well - but I have no ways of testing it.

Cheers

Sam

[Sergei Baklan]

@Sameer_Bhide , INDEX returns single 2D array, not array of arrays. As alternative it could be =FILTER(B1:D10, COUNTIF(E2:E4,A1:A10) )

[Peter Bartholomew]

Expand simply resizes the output array; it doesn't read across more values.

To return a reference to a larger range within the return array you could use the reference as the first parameter of an OFFSET.

= OFFSET(
    XLOOKUP(lookupValue,lookupArray,returnArray)
  ,0,0,3,3)

[Sergei Baklan]

@Peter Bartholomew , great idea. OFFSET is alive despite of all criticism.

[Timothy Calford]

I would love to see TEXTSPLIT split a single by character.  Either accept  TEXTSPLIT("word","") or make the delimiter optional so that TEXTSPLIT("word") gives w o r d.  This is standard behavior for javascript and other languages, I wonder why not in spreadsheets?

[Baccma]

@timothy_calford .  You can do this relatively easily with: =LET(text,"word",MID(text,SEQUENCE(1,LEN(text),1,1),1))

Wrap it in a lambda if you wish to use it often.

Not saying, there isn't a use for a CHARSPLIT type function to make it easier, but the functionality is already available.

[PGSystemTester]

These are terrific and will be a huge help. Since spill range, the number of questions on Stack Overflow that have popped up has grown increasingly. These will help significantly, particularly with combining arrays. 

[Sameer_Bhide]

@Peter Bartholomew

Why Offset(Volatile) , Index (Quasi Volatile) is already doing the same thing =INDEX(XLOOKUP,(E2:E4,A1:A10,B1:D10),SEQUENCE(3),SEQUENCE(,3,1,0))

[Sameer_Bhide]

Here is an interesting use of the new functions - You can roll out a CROSSJOIN as below

CROSSJOIN

=LAMBDA(vArray1, vArray2,
TEXTSPLIT(
TEXTJOIN(";",,TOROW(vArray1 & "," & TRANSPOSE(vArray2))),
",",";")
)

Usage  = CROSSJOIN(A1:A4,B1:B4) = will produce 16 elements 

Cheers

Sam

[Peter Bartholomew]

@Sameer_Bhide 

Like you, I tend to avoid volatile functions but, in reality, it only becomes an issue when the workbook exhibits significant calculation delays.  The OFFSET function returns a range object whereas

= INDEX(
    returnArray,
    SEQUENCE(3,1,XMATCH(lookupValue,lookupArray)),
    SEQUENCE(1,3)
  )

returns the same values, but as an array.

As an aside, I recently used a similar formula using MAP to return a sequence of single cell references from a minesweeper game and 

= OFFSET(cell,-1,-1,3,3)

to give the immediate vicinity to check for count of mines.

[Sergei Baklan]

Liam Bastick from time to time explains pros and cons of OFFSET, consider it as useful function, for example A great function for scenario analysis in Excel - FM (fm-magazine.com).

I didn't make any specific tests, but don't care to use volatile functions like OFFSET or TODAY. All depends on scenario.

[Sameer_Bhide]

@Peter Bartholomew 

When Index is used as a stand alone formula - It points to a Cell(s) and returns the .Value2 property of the Range

But when used on either side of the Range operator - point to a cell and returns the range reference.

So A1:INDEX(A:A,4) - Returns A1:A4 or INDEX(A:A,2):INDEX(A:A,4) returns A2:A4

Cheers

Sam

[Peter Bartholomew]

@Sameer_Bhide 

Even INDEX(A:A, 2) returns a single cell range. In fact, it is that is necessary for it to work and an operand of the colon ":" operator.  Interestingly, though 

= INDEX(rangeA,2)

is a range reference, wherreas

= INDEX(rangeA,{2})

is not.  CHOOSWROWS only returns arrays but MAP will return an array of individual range references, at least according to the ISREF function.

[DavidSDSU]

@JoeMcDaid - Totally get that this is not generally available per that closing paragraph...

In your experience, with your teams' rolling release schedules, how long (in general) is it from the time an announcement like this is made until features like these hit general availability? not looking for any hard dates - rather a general gut check.

Cheers,

David

[Sergei Baklan]

@Sameer_Bhide , as variant

CROSSJOIN
=LAMBDA(arrayA, arrayB,
    HSTACK(
        TOCOL(IF(SEQUENCE(, ROWS(arrayB)), arrayA)),
        TOCOL(IF(SEQUENCE(, ROWS(arrayA)), arrayB), , 1)
    )
)

[Sergei Baklan]

@DavidSDSU , it very depends on stats and feedbacks collected on beta stage. I guess it'll be safe to assume few months if under GA you mean Current channel.

[Sameer_Bhide]

@Sergei Baklan - Very Nice !! - Much better than my version - Going straight to my LAMBDA library.

Cheers

Sam

[Jen_Emerson5]

Hi !   I don't have any of these features yet but am using Excel 365 at work.   Is it possible that our IT needs to install a patch or fix (or  ???)  for us to have these new functions?   Now that I have seen them.....I cannot unsee them and I MUST have them!   ;)     Please help me join the cool kids who are already using these new features.

[DavidSDSU]

@Jen_Emerson5,  I feel the same way. Keep in mind these features like XLOOKUP() and LET() are announced before they are included in the final release. Per @Sergei Baklan 

How long (in general) is it from the time an announcement like this is made until features like these hit general availability?

Sergei Baklan MVP | ‎Apr 06 2022 02:24 PM

@DavidSDSU, it very depends on stats and feedbacks collected on beta stage. I guess it'll be safe to assume few months if under GA you mean Current channel

Last time with XLOOKUP and LET it hit Office365 online first about a month after announcement and about 8-12 weeks for the desktop version... again that can vary by release.

-David

[Peter Bartholomew]

@Jen_Emerson5 

If you have influence within your corporate IT, I believe it is possible for them to allow you access to beta release code (for evaluation purposes?).

[Sergei Baklan]

@Jen_Emerson5 , I'd do not recommend to use Beta in corporate environment for the production. First, you loss compatibility, Second, during Beta it could be changes introduced to any of these functions, like adding of new parameter or changing of their order or slightly different function behavior, whatever. 

[Peter Bartholomew]

The charts may have some interesting visual effects but I do not see the discussion as pertinent to the original topic of the blog post.

[Sergei Baklan]

@MLtechniques , could you please move your post into separate discussion. It's Microsoft Excel Community and Start New Discussion. Here it very interferes with reading of other AFE comments.

[Meenah_Khosraw]

@MLtechniques, 

As your comment doesn't relate to the content in the author's blog post, I'm moving it to the Excel discussion space. 

Thanks, 

Meenah

Community Manager | Tech Community Admin

[Viz]

I am a Beta user and I have access to these functions. But AFE shows error while using these functions. It works fine when I sync it. But it is an unnecessary distraction when you are searching for actual bugs.

[Sergei Baklan]

@Viz , more exactly what it shows is warning, they are marked the same way as errors. 14 new functions are on Beta only, not sure with full coverage now or not. AFE could be installed practically on any version. Perhaps if add functions to intellisence finding of errors will be harder on pre-Beta.

[jack-williams]

@Viz To resolve that I would need to add these functions to the known list of functions in AFE. For users in Beta this would be fine; for users not in beta it could mean writing a formula that references "VSTACK" which has no warning but returns #NAME! when evaluated. It would also mean users get these autocomplete suggestions when they do not have the functions. Unfortunately, there is no good way to determine what functions a user has within AFE. I might add a setting that lets you toggle support for these functions. I'll add an issue to the github issue tracker for this. Support Excel Beta functions · Issue #17 · microsoft/advanced-formula-environment (github.com)

[Peter Bartholomew]

Hi Jack. My preference would be for you to keep the AFE in line with the full beta rollout.  Users on slower channels might reasonably test for the existence of new functionality, by using the worksheet, before launching into experimentation using the AFE.

[Sergei Baklan]

@Peter Bartholomew , AFE is formula environment. Not only formulas with new functions. AFE is add-in which is deployed independently of Excel, has its own development cycle. Now it's even not beta, just concept. Not sure why AFE deployment shall be synced with Excel deployment.

As for me it's installed on all versions I'm working with, not only on Beta. If former time I used Notepad++ to edit long complex formulae, now it's AFE. From that point of view I'd prefer it'll be compatible with all versions, starting from at least Current, and with Excel for web.

[Peter Bartholomew]

@Sergei Baklan I am only partly convinced.  As yet, the AFE is a Name development environment within Excel.  To be a full formula environment it should also be capable of loading formulas to the active cell and inspecting the current content of such a cell, just like  the formula editor bar (and replacing it?).  Although the AFE will have its own development cycle, the declaration of legal functions will be language dependent and independent of the main development cycle?

@Viz was clearly irritated by the red warning underline whilst I have confidence in the fact that you will cope just fine without such warnings, even when you are supporting legacy Excel.  Not that I wish to make @jack-williams life any harder!

[Sergei Baklan]

@Peter Bartholomew , that's more linguistic question. It's not Name environment (in some scenarios I use it without assigning names and keeping formula in Name manager). And yes, it's not Full formula environment. That's just AFE, supporting tool which we may use this or that way depends on scenario of work.

[Henn Sarv]

Those function are super and long waited/wanted

Using in real life I find one minor problem and actual workaround. TEXTSPLIT don't work against array (#-reference)
3rd example shows the workaround

[Peter Bartholomew]

@Henn Sarv 

It's the dreaded 'array of array' problem, this time manifest as a truncated array operation rather than an error.  In my experience you can expect to hit this limitation in about 80% of the solutions you develop.  A fairly generic solution is offered by the use of thunks, combined with MAKEARRAY.  My worksheet formula is

= LET(
    splitNameϑ, MAP(fullName, LAMBDA(f, Thunkλ(TEXTSPLIT(f," ")))),
    MAKEARRAY(ROWS(fullName),2, Expandλ(splitNameϑ))
  )

backed by two utility functions used to form and expand thunks respectively

Thunkλ = LAMBDA(x, LAMBDA(x));

Expandλ = LAMBDA(ϑ,LAMBDA(r,c,
    INDEX(INDEX(ϑ,r,1)(),c)));

One day such workarounds may not be needed?

[kal_at_kalx_net]

An XLOPER with type xltypeMulti is recursive. To handle arrays of arrays I use handles. The subarrays are handles (funny looking doubles encoding the pointer to the sub-multi) and it is possible to call an add-in function to expand them. A bit clumsy, but it works.

Multi's having two columns where the first column is a string map quite nicely to JSON objects. See xll_inet for example. 

[Peter Bartholomew]

Would EXTEND by more descriptive of the proposed EXPAND function?  After all, he content doesn't expand in any sense to fill the extended range.

I suggest that EXPAND would be better applied to a function that expands a row or column array of Thunks to form a 2D array.  At the moment, turning Thunks back into legal formulations of arrays of arrays is a complicated process.  The opposite process, of forming a Thunk, could be CONDENSE.