Excel gets new text splitting powers

Annabelle AlmerLast updated: 20 March 2022

Microsoft 365,Microsoft Excel,Microsoft Office,Microsoft Office for Mac,Office 2021 / Office LTSC,Office 365

At long last, Microsoft has introduced new text splitting and manipulation functions in Excel, making it a lot easier to arrange, split and merge text.

Normally when working with text it’s common to need to break the text apart or put strings together. After twenty years plus, Microsoft has finally listened to Excel users.

We’ll guide you through some of the new formulae and how to use them. It’s easy to see if the new functions are in your Excel 365, in the formula bar type =text and see if the new functions appear, TextAfter, TextBefore and TextSplit.

Extracting Text Before or After

If you want to extract text from the beginning or end of a cell’s content, Excel has introduced two different functions that will basically return everything before or after a specified delimiter. These functions include:

TEXTBEFORE – Returns text that’s before the specified delimiting characters.
TEXTAFTER – Returns text that’s after the specified delimiting characters.

These two are a lot simpler than combining Left() or Right() functions with Search() to find the split position in the string.

TEXTBEFORE

Syntax

=TEXTBEFORE(input_text,text_before, [n], [ignore_case])

TEXTAFTER

Syntax

=TEXTAFTER(input_text, text_after, [n], [ignore_case])

Arguments

input_text – this is the text you are searching within; wildcard characters are not allowed.
text_before – this is the text that marks the point before which you want to extract.
text_after – this is the text that marks the point after which you want to extract.
[n] – the nth instance of text_before that you want to extract. By default, n=1. A negative number starts searching input_text from the end. Optional.
[ignore_case] – you can specify FALSE to make the search case-sensitive. This option is defaulted as TRUE which means the search is NOT case sensitive. Optional. Note to Microsoft – could you please avoid these kinds of ‘double-negative’ options.

TextAfter and TextSplit – Source:Microsoft

Use “ “(space) as a delimiter, as shown in the example above =TEXTAFTER(B2,” ”) to extract everything but the first word.

Microsoft like to show off text splitting by using first and last names. That works only if the names are just simple first and last with no variations. Here’s some ‘real world’ examples, see how TextAfter doesn’t work for Astronauts Swigert and Roosa as we’ve typed them.

The same problem happens with dual last names such as ‘del …’ or ‘von …’ etc.

That’s not to say TextAfter isn’t useful, just beware of problems not mentioned in simplistic and self-serving product demos.

Easier Splitting Text

An easy way to split text into many segments is using the new function:

TEXTSPLIT – Splits text into rows or columns using delimiters

The result is a dynamic array which can be as long as necessary either across a row or down a column.

If this seems familiar, it’s because TextSplit() is same as the Text-To-Columns wizard in Excel, but as a formula instead. And TextSplit() can work down by rows as well as across columns.,

TextSplit() is the opposite of the existing TEXTJOIN function already in Excel.

TEXTSPLIT

Syntax

=TEXTSPLIT(Input_Text, [col_delimiter], [row_delimiter], [Ignore_Empty])

Arguments

Input_Text – this is the text you want to split.
[col_delimiter] – this is one or more characters that specify where to spill the text across columns. This is optional, leave it empty if you want to split down a column.
[row_delimiter] – this is one or more characters that specify where to spill the text down rows. This is optional.
[Ignore_Empty] – if you specify TRUE, you will create an empty cell when two delimiters are consecutive. This feature is defaulted to FALSE, which won’t create an empty cell. This is optional but another annoying double-negative.

Note: the delimiter characters are removed from the result, which is normally what you’d want but is worth keeping in mind. See the ‘winter’ example on Row 13 above.