(Originally published on March 16, 2022 by Joe McDaid)
Hey, Insiders! My name is Joe McDaid, and I’m a Program Manager on the Excel team. 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.
TEXTSPLIT Function
- TEXTBEFORE - Returns text that’s before delimiting characters
- TEXTAFTER - Returns text that’s after delimiting character
- 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 Function
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.
TOCOL Function
- 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.
EXPAND Function
- 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.
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
We want to hear from you! Please go to Help > Feedback in Excel to provide your thoughts about this feature.
Learn about the Microsoft 365 Insider program and sign up for the Microsoft 365 Insider newsletter to get the latest information about Insider features in your inbox once a month!
Microsoft
