For decades, anyone working with text data in Excel has been limited to the same old functions for separating or parsing text strings. But recently, Excel added three new dynamic array text functions. And they are awesome! The new functions are designed to manipulate text data that have delimiters. The three new functions are TEXTBEFORE, TEXTAFTER, and TEXTSPLIT.


The TEXTBEFORE and TEXTAFTER functions are similar and use a similar syntax. The TEXTBEFORE function extracts the text from a string that occurs before the specified delimiter. The TEXTAFTER function extracts the text from a string that occurs after the specified delimiter.


=TEXTBEFORE(text,delimiter,[instance_num], [match_mode], [match_end], [if_not_found])

=TEXTAFTER(text,delimiter,[instance_num], [match_mode], [match_end], [if_not_found])

Text       The text you are searching for. Required.

Delimiter             The text that marks the point after which you want to extract. Required.

instance_num   The instance of the delimiter. If you have multiple instances of the same delimiter, choose the instance other than the first one. A negative number starts searching text from the end. Optional.

match_mode     Determines whether the text search is case-sensitive. Optional.

match_end         Treats the end of the text as a delimiter. Optional.

if_not_found     Value returned if no match is found. Optional.


For example, suppose I had a simple list of names with some data separated by commas. I could use the Text to Columns tool to separate the data into columns. But that is a tool. It is not dynamic. To make it dynamic (the results update automatically as the source data changes), I need to put formulas in each column. Previously, I had to determine the location of the delimiter and the length of the text I wanted. It was tedious using a combination of the RIGHT, LEFT, and MID functions with LEN, SEARCH, FIND, SUBSTITUTE, or REPLACE. There are a few ways to do this, but here is one way I could have done it using the old functions. Imagine how cumbersome this could be with 40 or 50 columns!

Column B would be =LEFT($A2,(SEARCH(“,”,$A2)-1))

Column C would be =MID($A2, SEARCH(“,”,$A2) + 1, SEARCH(“,”,$A2,SEARCH(“,”,$A2)+1) – SEARCH(“,”,$A2) – 1)

Column D would be =MID($A2, FIND(“#”,SUBSTITUTE($A2,”,”,”#”,2))+1, FIND(“#”,SUBSTITUTE($A2,”,”,”#”,3)) – FIND(“#”,SUBSTITUTE($A2,”,”,”#”,2))-1)

Column E would be =MID($A2,(FIND(“/”, SUBSTITUTE($A2,”,”,”/”, LEN(A2)-LEN(SUBSTITUTE($A2,”,”,””))))+1),10000)

Using the new TEXTBEFORE and TEXTAFTER functions is much easier. Here is one way to do it now:

Column B would be =TEXTBEFORE($A2,”,”)

Column C would be =TEXTBEFORE((TEXTAFTER($A2,”,”,1)),”,”,1)

Column D would be =TEXTBEFORE((TEXTAFTER($A2,”,”,2)),”,”,1)

Column E would be =TEXTAFTER($A2,”,”,-1)



The TEXTSPLIT function works similarly to the Text-to-Columns tool but in the form of formulas. It allows you to split across columns or down by rows.

=TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty], [match_mode], [pad_with])

Text       The text you want to split. Required.

col_delimiter      This character will be treated as the column separator. Required

row_delimiter    This character will be treated as the row separator. Optional.

ignore_empty    TRUE to ignore consecutive delimiters. Defaults to FALSE, which creates an empty cell. Optional.

match_mode     Determines whether the match is case-sensitive. Optional.

pad_with            The value with which to pad the result. The default is #N/A. Optional.

TEXTSPLIT results in a spilled array. Therefore, all cells where the results will be returned must be empty. Otherwise, Excel will return an error.


The TEXTSPLIT function is an improvement over Text to Columns because it is dynamic. Using the same example above, a simple list of names with data separated by commas is much easier with TEXTSPLIT. Here is how to do it.

Column B would be =TEXTSPLIT($A2,”,”)

TEXTSPLIT can also split delimited data into rows. Here is an example that splits the data into a new column for each equal sign delimiter and a new row for each comma delimiter.

A final example is to use TEXTSPLIT to split the date into its various parts and put each into a column.

Bottom Line

For anyone working with data in Excel, splitting values into separate columns and rows is usually only a first step before performing data analysis or data conversions. The three new text functions TEXTBEFORE, TEXTAFTER, and TEXTSPLIT make this task much easier.

Leave a Comment

Your email address will not be published. Required fields are marked *

Exit mobile version


Get great content like this delivered to your inbox!

It's free, convenient, and delivered right to your inbox! We do not spam and we will not share your address. Period!