New Text and Array Functions

Microsoft announce new Text and Array functions for Excel

Announced by Microsoft on 16th March 2022

In this latest article in our Future of Excel series we look at the new Text and Array functions announced for Microsoft Excel.

An exciting day for all us Excel users as Microsoft announce 14 new functions. And some of them look like they are going to be really useful, simplifying the way we tackle data analysis and financial modelling challenges.

TEXTBEFORE

TEXTBEFORE returns text from a text string that occurs before a given character or string.

=TEXTBEFORE(text,delimiter,[instance_num], [match_mode], [match_end], [if_not_found])

For example: =TEXTBEFORE("New Excel function"," function") returns "New Excel"


TEXTAFTER

TEXTAFTER returns text from a text string that occurs after a given character or string.

=TEXTAFTER(text,delimiter,[instance_num], [match_mode], [match_end], [if_not_found])

For example: =TEXTAFTER("New Excel function","New ") returns "Excel function"


TEXTSPLIT

TEXTSPLIT splits a text string by using column and row delimiters. The TEXTSPLIT function works the same as text to column, but in a formula rather than through a wizard. It allows you to split across columns or down by rows.

=TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty], [match_mode], [pad_with])

The ability to split text by formula across rows and columns is incerdibly useful.


VSTACK

VSTACK appends (combines) arrays vertically and in sequence to return a larger array.

=VSTACK(array1,[array2],...)


HSTACK

VSTACK appends (combines) arrays vertically and in sequence to return a larger array.

=HSTACK(array1,[array2],...)

 

TOROW

TOROW returns the array (range of cell) in a single row.

=TOROW(array, [ignore], [scan_by_column])


TOCOL

TOCOL returns the array (range of cell) in a single column.

=TOCOL(array, [ignore], [scan_by_column])


WRAPROWS

WRAPROWS wraps the provided row or column of values by rows after a specified number of elements to form a new array.

=WRAPROWS(vector, wrap_count, [pad_with])


WRAPCOLS

WRAPCOLS wraps the provided row or column of values by columns after a specified number of elements to form a new array.

=WRAPCOLS(vector, wrap_count, [pad_with])


My thoughts

These are a great set of new Excel functions and a really useful addition to any data analyst's or financial modeller's toolkit.

The stand outs for me are TEXTSPLIT, TOROW and TOCOL. I can't wait for these functions to be in general Excel usage.

See more about these new Excel functions on the Microsoft Excel Tech blog here.

Release date

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.

They will be tested by the insider team and then released to all Excel users once finalised.


Our Future of Excel series

This series of articles focuses on new features soon to be released on Excel. This is based on announcements by the Microsoft Office Insider team.

See other articles in our Future of Excel series here.

 
Back to Blog