manipulation functions

(Originally posted March 16, 2022 By ~ Joe McDade)

Hello, Insiders! I’m Joe McDaid and I’m a Program Manager on the Excel team. I’m excited to share with you 14 new Excel functions designed to make it easier to manipulate text and arrays in your worksheets.

Text manipulation functions

When working with text, a common task that you will need to complete is to “split” a string of text using a delimiter. You can already do this using a combination of SEARCH, FIND, LEFT, RIGHT, MID, SUBSTITUTE, and SEQUENCE, but many of you have told me that it can be difficult to use.

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

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

TEXTSPLIT function

• TextBEFORE – Returns the text before the delimiter character.
• Text After – Returns the text following the delimiter character.
• Text Splitting – Split text into rows or columns using delimiters.

Array manipulation functions

Since the introduction of dynamic arrays in 2019, the use of array formulas has grown significantly. To make it easier to build compelling spreadsheets using dynamic arrays, we are releasing a collection of 11 new array manipulation functions.

Array join

Combining data can be difficult, especially when the source size is flexible. VSTACK and HSTACK make it easy to combine dynamic arrays and stack data vertically or horizontally.

VSTACK function

• V stack – Stack the arrays vertically.
• Hstack – Stack the array horizontally.

Array formation

Changing the “shape” of data in Excel has been difficult, especially changing from an array to a list and back again. If you want to convert a two-dimensional array to a simple list, use TOROW and TOCOL to convert a two-dimensional array to a single row or column of data.

The WRAPROWS and WRAPCOLS functions work in reverse: they “wrap” the data to the next line, like the text in this document, when the selected width/height limit is reached, creating a two-dimensional array of the specified width or height.

TOCOL function

• Torrow – Returns an array as one row.
• Tokol – Returns an array in one column.
• Rap Prowse – Wraps the row array into a two-dimensional array.
• Rapcalls – Wraps the array of columns into a two-dimensional array.

Resize array

Is your array too big? No problem. Enter the TAKE and DROP functions! These functions allow you to reduce an array by specifying the number of rows to keep or remove from the beginning or end of the array.

Similarly, you can use CHOOSEROWS or CHOOSECOLS to select specific rows or columns from an array based on their index.

EXPAND lets you grow an array to any size you want. Just provide the new dimensions and the values ​​to fill the extra space.

Extensions

• Take it – Returns a row or column from the beginning or end of an array.
• get off – Delete rows or columns from the beginning or end of an array.
• Selector Row – Returns a specified row from an array.
• Supersecols – Returns a specified column from an array.
• Expand – Extends an array to the specified dimensions.

Scenarios to try

• Use ” ” (space) as a delimiter to extract the first name using TEXTBEFORE and the last name using TEXTAFTER.
• Use TEXTSPLIT to split the names into an array using ” ” (space) as a delimiter.

If you want to combine two data ranges:

• Combine two ranges of data vertically using VSTACK.
• Combine two ranges horizontally using HSTACK.

effectiveness

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

Features are released over time to ensure everything works smoothly. They are released slowly to a larger number of Insiders, highlighting features you may not have. Sometimes we remove elements based on your feedback to make them even better. This is rare, but we also have the option to completely remove a feature from the product, even if you, as an Insider, had the opportunity to try it out.

feedback

We want to hear from you! Go to: help > feedback Please let us know what you think about this feature.

Learn about Microsoft 365 Insider Program And join us Microsoft 365 Insider Newsletter Get the latest Insider features straight to your inbox once a month!