Excel’s New Text Functions Make Data Parsing a Breeze

Parsing data in Excel can be complicated, using a combination of functions ranging from LEFT, RIGHT, MID, and FIND. However, with the help of a few new functions that are available in Excel, the process is a whole lot easier for users. In this post, I’ll look at how you could parse out a date that is formatted as text using the new functions and comparing that with how you might have done it using the old functions.

In this example, I’m going to try parse out the numbers I need to convert the following value, which is reading as text:

This date is April 19, 2022. But because my regional settings are set to month/day/year this value doesn’t compute properly since it would be looking for a 19th month.

Pulling the day value (data before the delimiter)

The old method

The first number in the date value above relates to the day of the month. Using the LEFT function in Excel, you could use something like this:

=LEFT(X,2)

Where X is the cell value. That will pull the first two characters in the string. But in some cases there might only be one day for the date. And for that reason, I’m not going to hardcode the number of characters. The best approach (under the old method) is by using the FIND function to locate where the delimiter (“/”) is. The more versatile formula would look as follows:

=LEFT(X,FIND("/",X,1)-1)

The new method

One of Excel’s new text functions is called TEXTBEFORE. And as the name suggests, it will extract all the text that comes before a delimiter. Without needing the FIND function, I can simply do this to extract the day value:

 =TEXTBEFORE(X,"/")

Pulling the year value (data after the delimiter)

The old method

To grab the year in the date I could cheat and use the RIGHT function and just grab the last four numbers. But that wouldn’t be flexible enough in the event that I might have 2 digits instead of 4 as the year. This can get messy as now I have to use multiple FIND functions in order to determine the length. The key is to take the length of the function and subtract from that the position of the second delimiter. Here’s what that looks like:

=RIGHT(X,LEN(X)-FIND("/",X,FIND("/",X,1)+1))

The nested FIND functions can get a bit complicated. Here you’ll see even more efficiency with Excel’s new functions.

The new method

The TEXTAFTER function can greatly simplify this action because you can specify after which delimiter you want to pull the characters; there is no need to have nested functions with this:

=TEXTAFTER(X,"/",2)

In this formula, the characters after the second “/” will be extracted. Note: both the TEXTBEFORE and TEXTAFTER functions allow you to specify the instance of the delimiter (i.e. it doesn’t always need to be the first one).

Pulling the month value (data between delimiters)

The old method

The most challenging part of this process is undoubtedly to pull the data between delimiters. In this example, I’ll need to use the MID function and use nested FIND functions to determine the space in-between the delimiters. It’s an ugly formula if you don’t rely on hardcoding:

=MID(X,FIND("/",X,1)+1,FIND("/",X,FIND("/",X,1)+1)-FIND("/",X,1)-1)

That’s four FIND functions in one formula. You can quickly see how parsing out this information can be a challenge. But with the new Excel functions, it’s much easier to do this.

The new method

There isn’t a new function that specifically pulls the values between delimiters. But by using both the TEXTAFTER and TEXTBEFORE functions, you can do exactly that. Let’s start with just grabbing everything after the first delimiter:

TEXTAFTER(X,"/")

This will give us the following result: 4/2022. Obviously that’s not what I want. But now, I can nest this within the TEXTBEFORE function, and grab the value before that other “/” with the following formula:

=TEXTBEFORE(TEXTAFTER(X,"/"),"/")

We are still dealing with a nested function here, but this is no doubt easier than all those FIND functions under the old method.

Using an array function

Another option that you can use is to extract all the values between the delimiters using the TEXTSPLIT function. Simply enter the following formula:

=TEXTSPLIT(X,"/")

Then the values will be extracted into three cells, one for the day, month, and year:

The benefit of this approach is you can quickly pull everything from the cell you’re parsing data from.

Regardless of which option you choose, Excel has given its users some new tools that can make the parsing much easier and less complicated than it was before.

If you liked this post on Excel’s New Text Functions, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.