Splitting data into columns using the new TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

Sep 29, 2022 | Excel Hints and Tips

There have been a number of different ways to extract data from a single column into multiple columns in Excel for a long time but these new functions of TEXTSPLIT, TEXTBEFORE and TEXTAFTER will often makes life easier and more flexible. 

TEXTSPLIT Function 

This works in a similar way to the Text to Columns option but using a formula. It allows you to split across columns or down rows. 

Structure 

=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with]) 
Only the first 2 parts of the function are required but the other parts give added flexibility.  

Text – The text you want to split 
Col Delimiter – The text that marks the point where to spill the text across columns 
Row delimiter – The text that marks the point where to spill the text down columns 
Ignore Empty – Specify FALSE to create an empty cell when two delimiters are consecutive. Defaults to TRUE, which creates an empty cell 
Match Mode – Searches the text for a delimiter match. By default, a case-sensitive match is done 
Pad With – The value with which to pad the result.  The default is #N/A 

Example 1 

TEXTSPLIT 1

In this example the full name is split into columns to show the first name and surname.  The split is made based on the space delimiter.  Only the calculation for the first name needs to be created and it automatically spills to the last name column.  This can then be autofilled. 

Example 2 

TEXTSPLIT 2

In this example the full name is split by the row using the space delimiter.  The column part of the function is left empty as it is irrelevant in this case. 

TEXTBEFORE Function 

This function returns the text before a specified delimiter 

Structure 

=TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]) 
Only the first 2 parts of the function are required. 

Text – The text you are searching within 
Delimiter – Marks the point where you want to extract 
Instance num – The instance of the delimeter you want to extract.  The default is 1.  A negative number will search for the text from the end 
Match mode – By default this is case sensitive.  If 0 is entered it is also case sensitive, if 1 is entered it is not 
Match End – Treats the end of the text as a delimiter 
If not found – Value that is returned if the delimiter is not found.  By default #N/A is returned 

Example 

TEXTBEFORE

In this example the text before the space is extracted producing the result Mo. 

TEXTAFTER Function 

This function returns the text after a specified delimiter 

Structure 

=TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]) 
Only the first 2 parts of the function are required. 

Text – The text you are searching within 
Delimiter – Marks the point where you want to extract 
Instance num – The instance of the delimeter you want to extract.  The default is 1.  A negative number will search for the text from the end 
Match mode – By default this is case sensitive.  If 0 is entered it is also case sensitive, if 1 is entered it is not 
Match End – Treats the end of the text as a delimiter 
If not found – Value that is returned if the delimiter is not found.  By default #N/A is returned 

Example 

TEXTAFTER

In this example the text after the space is extracted giving the result Shah. 

Further Reading

If you’ve enjoyed reading about how to split data in Excel using the new TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions, there’s some other blogs below that you might find useful:

Want to learn more about Microsoft Excel? Then email lara@laramellortraining.co.uk to discuss how I can help or have a look at the Microsoft Excel Courses I run.