Split Text in Excel the Easy Way: TEXTSPLIT, TEXTBEFORE and TEXTAFTER Explained

Annabelle AlmerLast updated: 11 July 2026

Microsoft 365,Microsoft Excel,Microsoft Office,Microsoft Office for Mac,Office 2024,Office 365

Splitting text in Excel used to mean wrestling with nested LEFT, RIGHT, MID and SEARCH formulas. Not any more. Excel 365 and Excel 2024 have three functions that do the job in one step: TEXTBEFORE, TEXTAFTER and TEXTSPLIT. TEXTBEFORE grabs everything before a delimiter, TEXTAFTER grabs everything after it, and TEXTSPLIT breaks a whole string into a spilled array across columns, down rows, or both at once. Here is exactly how each one works, which Excel versions have them, and the real world traps that Microsoft’s tidy first name and last name demos quietly leave out.

Working with text usually means breaking it apart or joining it together. After more than twenty years, Microsoft has finally listened to Excel users.

We’ll guide you through the new formulas and how to use them. To check whether you have them, type =text into the formula bar and look for 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 return everything before or after a specified delimiter. These functions are:

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 or after 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 default to TRUE which means the search is NOT case sensitive.
Optional. Note to Microsoft – could you please avoid these kinds of ‘double-negative’ options?

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

Microsoft likes 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 are some ‘real world’ examples, see how TextAfter doesn’t work for astronauts Swigert and Roosa because of the middle name or nickname.

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

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 to use 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 the 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] – ignore_empty = TRUE ignores empty values, so no empty cell is created. FALSE, the default, does create an empty cell for consecutive delimiters.

Note: the delimiter characters are removed from the result, which is normally what you’d want but is worth keeping in mind.