Bottom Line: Learn the pros and cons of the new text splitting functions in Excel, and which one is my favorite.
Skill Level: Intermediate
Watch the Tutorial
Download the Excel File
The practice sheet that I use in the video can be downloaded here.
New Functions for Splitting Text
Microsoft has recently released some great new functions for splitting out text from one column into multiple columns.
These new functions are currently only available for the Insider Beta Channel for Microsoft 365, but should hopefully be rolling out to other channels in the coming months. Until then, if you are looking for ways to split text, check out my tutorials for splitting text using Power Query, Text to Columns, formulas, or Flash Fill.
The first of these functions is TEXTSPLIT, which looks for any instance of a specified delimiter in a cell and then spills a range for each text group between those delimiters. What's a delimiter? It's basically a symbol or punctuation that is used to separate words or text groups. The most common delimiters are commas and spaces.
The other two functions are frequently used together. The first one is TEXTBEFORE, which returns any text that occurs before your specified delimiter. The second one is TEXTAFTER, which (as you can guess) returns the text that happens after a delimiter.
TEXTSPLIT offers some awesome options and advantages, but I've actually found myself favoring TEXTBEFORE and TEXTAFTER and using them more frequently. You'll see why as we look at how to use these excellent new functions.
TEXTSPLIT
The TEXTSPLIT function is really easy to use. There are only two arguments that you need to identify. The first is the text that you want to split. The second is the column delimiter (col_delimiter). In our simple example, I've used the cell in column A as the text, and a space (“”) as the delimiter.
Because this is a dynamic array function, it will separate the text wherever it detects a space and spill each of those text groups into its own column. In our example, there are only two groups of text (first name and last name), so it returns two columns. You can drag the fill handle down from the first cell to the cells below to copy down the formula/results.
This fast and easy function is awesome, but one of its biggest limitations is that it can't be used with Excel Tables. That's because Excel Tables can' handle spill ranges (at least, not yet). So when working with Excel Tables, a better option is to use TEXTBEFORE and TEXTAFTER.
TEXTBEFORE & TEXTAFTER
Just like with TEXTSPLIT, when writing our formula, we only need to identify the text that we want to pull from and the delimiter we want to split at. TEXTBEFORE will return whatever group of text is found before the delimiter and TEXTAFTER will return the text after.
So for our First Name column, we will use TEXTBEFORE because the first name comes before the space (our delimiter). For the Last Name column, we will use TEXTAFTER.
And as you can imagine, the same process is used for TEXTAFTER in the Last Name column.
Multiple Delimiters
All of that is simple to understand and execute because it's a very basic example. But what about when we have some entries that have more than two names? In some lists, a middle name or multiple middle names might appear.
Using TEXTSPLIT will place each of those names in a separate column, but as you can see, because there is variation in the number of names (some rows have 2, others 3 or 4), the names do not always line up with the proper heading.
Obviously, that is not the best solution. Let's see how TEXTBEFORE and AFTER can help.
Start with the First Name
To make the First Name column, we use the same process that we used previously. We pull the first name out by using TEXTBEFORE.
Then take care of the Last Name
To make the Last Name column, it's a little bit different. We will use TEXTAFTER, but because there can be more than one delimiter, we want to pull the text after the last delimiter. So we will add one more argument to our formula: instance number. If we specify a negative 1 as our instance number, Excel will pull the text after the first instance moving backward through the text string. So no matter how many names appear in the cell, it will always pull the last one.
Identify the Middle Name(s)
Pulling out the middle name(s) into a separate column gets a little bit trickier in terms of the formula we need to write. We will use both TEXTBEFORE and TEXTAFTER in the same formula.
- The formula starts by using TEXTAFTER to pull out all of the text that comes after the first space (thereby removing the first name).
- Then it uses TEXTBEFORE to pull out anything that is before the last space (removing the last name).
- Finally we wrap all of that in an IFERROR function, essentially telling Excel to return a blank if there is an error message. Why would there be an error message? If there is nothing between the first name and the last name (the entry only has two names), Excel doesn't have anything to return, so it returns an error.
The formula looks like this:
Splitting Other Types of Text
The functions aren't only useful for splitting names. In the example below, I want to separate file names from file extensions. As before, we can use TEXTAFTER with a negative 1 to pull out the text that comes after the last instance of the delimiter. In this case, the delimiter is a period instead of a space.
That formula is much more simple that the old way of doing it. My formula before TEXTAFTER would have looked like this:
=RIGHT(A5,LEN(A5)-FIND(“”,SUBSTITUTE(A5,”.”,””,LEN(A5)-LEN(SUBSTITUTE(A5,”.”,””)))))
Splitting into Rows Instead of Columns
One of the great things about TEXTSPLIT is that you have the ability to split text into rows, not just columns. You do this by using another one of the optional arguments in the formula: row delimiter. In my example below, I can turn a cell with several comma-separated items into a list.
Splitting into Rows AND Columns
Something even more impressive is the fact that TEXTSPLIT can simultaneously split text into both rows and columns. In the example below, colons are used to split the text into columns while commas are used to separate the text into rows.
Related Posts
If you don't have the new functions yet, we have tutorials for splitting text using Power Query, Text to Columns, formulas, or Flash Fill.
If you are interested in seeing what other new functions have recently come out, check out this post:
14 New Excel Functions: Text Split and Array Processing
And this tutorial walks you through how dynamic array formulas and spill ranges work in more detail:
Dynamic Array Formulas & Spill Ranges
Conclusion
TEXTSPLIT, TEXTBEFORE, and TEXTAFTER are all great “everyday” functions that you can in a lot of data cleansing tasks.
Since getting these new functions a few weeks ago, I have found myself using TEXTBEFORE and TEXTAFTER more frequently. Mostly due there compatibility with Excel Tables and ability to do more advanced extraction, as I showed in the middle name example above.
Can you see yourself using TEXTSPLIT, TEXTBEFORE, and TEXTAFTER? Let me know what you think of these functions and feel free to ask questions in the comment section.
Hallo John, ja leider werden wir da noch länger warten müssen bis MicroSoft auch uns (Excel in German) zur Verfügung stellt.
Ich finde gerade Textbefor und Textafter als sehr brauchbar, aber leider derzeit in meinem Excel MS-365 nicht verwendbar.
Sorry to hear you have to wait for the functions on the German version of Excel. They are definitely worth the wait! 🙂
Nice. But I routinely need to split full names with all kinds of anomalies. Here are some examples:
First Middle1 Middle2 Last
First Last Jr.
First Last Sr., MD
First Middle Last, RN, RNP, AACP
First Middle Last1-Last2
First First Middle Last
First Middle Last1 Last2, DDS
Name splitting is a chore… 🙁
Hi Allen,
Once nice feature of the new text functions is that you can specify multiple delimiters. You do this by creating a comma separate list of delimiters wrapped in braces.
{” “,”, “,”-“}
I agree that name splitting is still a chore and some scenarios need additional work. But the multiple delimeter option can come in handy.
Hi, it’s not a comment but a question, may i please know the formula of deliting a first “0” in cell number (e.g 084 4705 03 9)using a text function.
Great question! There are several ways to go about it. Here is one possible solution.
=IF(VALUE(LEFT(A1,1))=0,RIGHT(A1,LEN(A1)-1),A1)
This formula checks if the first character is 0 using the LEFT function. The VALUE function converts the text to a number before doing the comparison.
If there is a 0 at the beginning, the RIGHT function is used to return the remaining characters by finding the number of characters with LEN and subtracting 1.
I hope that helps. Thanks again and have a nice day! 🙂
Hi Jon
This, like a lot of your video’s, is really helpful. Unfortunately, my copy of Excel 365 does not have these functions. I cannot find an explanation online. What should I do?
In your first example, if you replace “A5” with “A5:A18”, the function only returns the first word of each cell, instead each word of each cell being split across the columns (e.g., “Ardisj” instead of {“Ardisj”,”Flindall”}).
Modern Excel normally handles array inputs very well, so I’m not sure if this is a bug or if there’s another trick needed to make this work.
Any thoughts?
A comparison of textbefore and after and textsplit vs Flash Fill would be useful, as there looks to be a lot of overlap.