Extracting and Splitting Text
Extracting and splitting text is about to get a lot easier.
Whilst Excel has functions like LEFT, MID, RIGHT, LEN, and SEARCH, unless you have a simple scenario, you’ll probably have to construct an unwieldy formula. Alternatively, you might use Text to Columns, Flash Fill or Power Query, but none of these is a perfect replacement.
Microsoft recently announced the arrival of several new functions, including TEXTBEFORE, TEXTAFTER and TEXTSPLIT. These are currently only available to Office Insiders on the Beta Channel.
Syntaxes
- TEXTBEFORE — returns text that’s before delimiting characters.
TEXTBEFORE ( text , delimiter , [instance_num] , [ignore_case] )- TEXTAFTER — returns text that’s after delimiting characters.
TEXTAFTER ( text , delimiter , [instance_num] , [ignore_case] )- TEXTSPLIT — splits text into rows or columns using delimiters.
TEXTSPLIT ( text , col_delimiter , [row_delimiter] , [ignore_empty] , [pad_with] )Examples
On page 1, D3 contains the string ‘Andrew Charles Moss’. Below it, several current and new text extraction methods are shown in the respective tables.
Notice how frustratingly long the current methods are compared to the new ones? That’s because the former are based on finding the position of each space to determine where to extract from and to — as well as the number of characters to return. Moreover, the excessive use of SEARCH just to extract ‘Charles’ epitomises the complexity required for middle values.
In contrast, TEXTBEFORE and TEXTAFTER only need text and a delimiter supplied. This works for ‘Andrew’ and ‘Charles Moss’, but ‘Andrew Charles’ and ‘Moss’ also have an [instance_num] of 2 to start the extraction from the second space. Furthermore, ‘Charles’ is extracted — first by using TEXTSPLIT to separate each part — and then INDEX to return the second array element.
Page 2 shows a current way of splitting text. The page 1 formulas for the first, middle and last names are placed in the CHOOSE function, which contains {1,2,3} in the index_num argument to spill them across the cells.
The aforementioned TEXTSPLIT does the same thing, but with a significantly shorter formula.
