New Text and Array Functions

Microsoft announce new Text and Array functions for Excel

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 incredibly 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.