Excel Update: 14 New Text and Array Functions to Revolutionize Data Handling

On March 16, Microsoft announced a significant update to Excel, introducing 14 new functions specifically designed for advanced array manipulation and text processing. For data analysts and power users, this update represents a major leap forward, streamlining complex tasks that previously required convoluted formulas or VBA scripts.

This strategic update focuses on making formulaic operations more intuitive and dynamic. By enhancing how Excel handles text strings and arrays, Microsoft is effectively narrowing the gap with Google Sheets, which has historically held an edge in native array visualization. While Excel introduced Dynamic Arrays recently, this latest batch of functions—currently available to Office Insiders—aims to make these powerful tools accessible for everyday data scenarios.

Mastering Text Manipulation with New Functions

Text processing in Excel has traditionally been a pain point for users. Splitting strings or extracting specific segments often required nesting multiple functions like LEFT, RIGHT, MID, FIND, SEARCH, and SUBSTITUTE. These formulas were not only difficult to write but also prone to errors if the data structure changed slightly.

The new update introduces three dedicated text functions designed to replace those complex legacy workarounds:

• TEXTBEFORE: Extracts all text that occurs before a specified delimiter.
• TEXTAFTER: Returns the text string found after a specified delimiter.
• TEXTSPLIT: Splits a text string into an array across columns or rows based on delimiters.

Excel spreadsheet demonstrating the new text functions TEXTBEFORE TEXTAFTER and TEXTSPLIT

The TEXTSPLIT function, in particular, is a game-changer. It effectively functions like the “Text to Columns” wizard but acts dynamically within a formula. This means if your source data updates, your split data updates automatically without needing to re-run a wizard.

Next-Level Array Processing

The bulk of this update—11 out of the 14 new functions—is dedicated to array management. As Excel moves towards a “grid-everywhere” calculation engine, these functions allow users to manipulate blocks of data (arrays) as easily as single cells. These tools enable the creation of massive, dynamic datasets from a single formula input.

Combining Arrays: VSTACK and HSTACK

One of the most requested features has been a simple way to combine data ranges. Previously, users had to rely on Power Query or complex index/match workaround to append data.

• VSTACK (Vertical Stack): Stacks arrays or ranges vertically, one on top of the other. This is ideal for consolidating lists from different sheets into a master list.
• HSTACK (Horizontal Stack): Appends arrays or ranges horizontally, placing them side-by-side.

Animation showing data arrays being merged vertically and horizontally

These functions support Dynamic Arrays, meaning the resulting stacked table will automatically spill into adjacent cells and resize if the source data grows.

Diagram illustrating how VSTACK and HSTACK combine data blocks

Reshaping Data Structures

Data cleaning often involves changing the orientation of data. While TRANSPOSE has existed for years, it only flips axes. The new reshaping functions offer granular control over how data is laid out:

• TOROW: Transforms a multi-dimensional array into a single row.
• TOCOL: Converts a multi-dimensional array into a single vertical column.
• WRAPROWS: Takes a long row or column of data and wraps it into a 2D array based on a specified number of values per row.
• WRAPCOLS: Similar to WRAPROWS, but wraps data to fill columns first.

These functions are particularly useful when dealing with raw data exports that are formatted poorly for analysis, allowing users to “reflow” data into a usable table format instantly.

Visual guide to array shaping functions including TOROW and WRAPCOLS

Resizing and Extracting Data

The final group of functions allows users to dynamically extract specific parts of an array or adjust its size. This is crucial for building dynamic dashboards where you might only want to show the “Top 5” results or remove headers programmatically.

• TAKE: Extracts a specified number of contiguous rows or columns from the start or end of an array.
• DROP: Removes a specified number of contiguous rows or columns from the start or end of an array, returning the rest.
• CHOOSEROWS: Returns specific rows from an array based on their index numbers.
• CHOOSECOLS: Returns specific columns from an array, allowing you to reorder or filter columns dynamically.
• EXPAND: Grows an array to a specified dimension, padding the new cells with a specific value (like “N/A” or 0).

Illustration of resizing functions showing how to select or exclude data parts

Availability and Version Requirements

It is important to note that these functions are currently rolling out and may not be immediately available to all users. As of the announcement, they are in the Beta Channel for Office Insiders.

To access these features, your system must meet the following build requirements:

• Windows: Version 2203 (Build 15104.20004) or later.
• Mac: Version 16.60 (Build 22030400) or later.

Conclusion

The addition of these 14 functions marks a pivotal moment for Excel’s development. By simplifying text extraction and offering robust array manipulation tools natively, Microsoft is empowering users to build more resilient, readable, and efficient spreadsheets. These tools not only reduce the need for complex “mega-formulas” but also significantly lower the barrier to entry for advanced data manipulation tasks. Users are encouraged to join the Office Insider program to test these features and prepare for their eventual general release.

References

• Microsoft Tech Community. (2022). Announcing New Text and Array Functions.
• Excel Official Blog. (2022). Dynamic Arrays and New Calculation Engine.