This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Fourteen new functions coming to Excel Part two of a three-part series of blogs
Get a preview of the 3 text functions and 11 array functions coming to your version of Excel very soon! Fourteen new functions coming to Excel New text functions (TEXTBEFORE, TEXTAFTER, TEXTSPLIT) (this blog) New functions for array manipulation

Fourteen new functions coming to Excel

Part two of a three-part series of blogs

Get a preview of the 3 text functions and 11 array functions coming to your version of Excel very soon!

Fourteen new functions coming to Excel
New text functions (TEXTBEFORE, TEXTAFTER, TEXTSPLIT) (this blog)
New functions for array manipulation

Posted by Andy Brown on 20 April 2022

In this blog

Extracting text before or after a delimiter
Splitting text using a delimiter

New text functions (TEXTBEFORE, TEXTAFTER, TEXTSPLIT)

Suppose you have the following data:

You want to get each person's first name and surname, the start of their postcode and the things they like.

Previously you would have to do the first part of this by using FIND or SEARCH to find the position of the first space in a list, then using LEFT, MID or RIGHT combined with LEN to extract data.

Extracting text before or after a delimiter

You can now solve the above problem using these nifty functions:

Function	What it does
TEXTBEFORE	Extracts text before a specified delimiter
TEXTAFTER	Extracts text after a specified delimiter

Both functions have these arguments:

Argument	Name	Type	Contains
1	text	Compulsory	The text you're extracting from.
2	delimiter	Compulsory	The delimiter you're searching up to/from.
3	instance_num	Optional	Which instance of the delimiter to use (by default you extract up to or from the first instance that you find). This can even be negative, as in the example below.
4	ignore_case	Optional	Whether you want to make ignore case-sensitivity (by default you don't, so everything is case-sensitive).

So here's how you could extract the names of each person:

This assumes that a range called Person has been created to refer to A2:A4).

You can use a negative instance to search from the end of a string, rather than from the beginning:

This function will extract text from the last backslash character onwards (or to put it another way, from the first backslash character encountered when you read from the end of the string of text).

Splitting text using a delimiter

This is a function which returns multiple values which spill into a dynamic array (something which wouldn't have been possible in old versions of Excel). Here's an example:

The function splits the list of likes into the component words.

The TEXTSPLIT function has these arguments:

Argument	Name	Type	Contains
1	text	Compulsory	The text you're extracting from
2	col_delimiter	Compulsory	The delimiter you're using to split the text
3	row_delimiter	Optional	An optional row delimiter (see below for what this means)
4	ignore_empty	Optional	Whether to create a blank cell if a delimiter appears twice in a row (the default is not to)
5	pad_with	Optional	What to put in any blank cells (this is only relevant if you include the row delimiter - see below)

Here's an example of the use of a row delimiter:

When the function encounters a \ character it moves on to a new column; when it meets a space, it moves on to the next row. Any unused cells are filled with xxx (normally they would show #N/A).

While I can easily see that splitting text across columns might be useful, I'm struggling to see why you'd also want to split it down rows!