Episode 2492
The Calc team at Microsoft released three changes to new functions in late May 2022. The improvements to TEXTBEFORE and TEXTAFTER give you options for dealing with situations where the delimiter that you are searching for is not found.
Table of Contents
(0:00) Former problem with TEXTBEFORE
(0:37) New syntax
(1:11) Using Match End
(1:31) Using If Not Found
(1:47) Why both?
(2:12) Thanks Excel Team
(2:27) Nancy Faust
(2:34) Like & Subscribe
(2:41) Excel Video Courses
The Calc team at Microsoft released three changes to new functions in late May 2022. The improvements to TEXTBEFORE and TEXTAFTER give you options for dealing with situations where the delimiter that you are searching for is not found.
Table of Contents
(0:00) Former problem with TEXTBEFORE
(0:37) New syntax
(1:11) Using Match End
(1:31) Using If Not Found
(1:47) Why both?
(2:12) Thanks Excel Team
(2:27) Nancy Faust
(2:34) Like & Subscribe
(2:41) Excel Video Courses
Transcript of the video:
Oh, there are some great improvements to the TEXTBEFORE and TEXTAFTER functions.
This is great - these functions are both very new.
Still in Insiders Fast, but we had the problem before.
If we were looking for a particular delimiter, for example a space in order to get first name out of this name.
That worked great as long as there was a space.
When we double Click to copy it down, anyone who just has a single name, they are getting an #N/A error.
And the workaround that I proposed before was that we'd have to use the TEXTBEFORE of A3 and the delimiter.
Right, kind of a pain that we would have to do that, but it certainly worked well.
Great news on May 26th or so, they changed three functions.
TEXTBEFORE and TEXTAFTER get new arguments called Match End which says that if we get to the end of the search and it hasn't found a delimiter, assume that the end of the cell is the delimiter.
And also if not found.
They also changed TEXTSPLIT - this is a minor change. They changed match mode from ignore case.
It does the exact same thing though. They just renamed the argument.
Same thing here match mode, used to be ignore case.
So here, let's check this out. We're looking through A6 looking for the space.
The instance number and match mode are both empty. But match end is set to 1 or True.
And doing nothing for if not found. Click OK.
Double Click to copy that down and you see the people with just a single name - it continues to work. We can also solve this with the If-not-found.
So here an extra comma and then in the If_Not_Found says “hey if there was no space just give us the entire cell”.
Double click and copy that down, so that's another way to work around. And then the question: why give us both of these?
Why give us match end and if not found?
There might be a time, I would guess, where when there is no space we would want to report an error.
So here instead of repeating A6, something simple like there isn't a space.
And then for all those people with just a single name and no delimiter, we can report that.
Thanks to the calc team in Redmond for giving us this great improvements.
It makes TEXTBEFORE and TEXTAFTER match with how Power Query handles TEXTBEFORE and TEXTAFTER.
And gives us just a few extra options for how to deal with those situations where the delimiter is not found. Hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
If you like these videos, please, down below, Like, Subscribe, and Ring the bell.
Feel free to post any questions or comments down in the comments below.
Now if you love Excel, check out my new courses on the Retrieve platform.
They are video courses but you just type what you're looking for.
It takes you right to that spot in the video and there's a complete transcript in several languages. It 's a super fast way to learn.
This is great - these functions are both very new.
Still in Insiders Fast, but we had the problem before.
If we were looking for a particular delimiter, for example a space in order to get first name out of this name.
That worked great as long as there was a space.
When we double Click to copy it down, anyone who just has a single name, they are getting an #N/A error.
And the workaround that I proposed before was that we'd have to use the TEXTBEFORE of A3 and the delimiter.
Right, kind of a pain that we would have to do that, but it certainly worked well.
Great news on May 26th or so, they changed three functions.
TEXTBEFORE and TEXTAFTER get new arguments called Match End which says that if we get to the end of the search and it hasn't found a delimiter, assume that the end of the cell is the delimiter.
And also if not found.
They also changed TEXTSPLIT - this is a minor change. They changed match mode from ignore case.
It does the exact same thing though. They just renamed the argument.
Same thing here match mode, used to be ignore case.
So here, let's check this out. We're looking through A6 looking for the space.
The instance number and match mode are both empty. But match end is set to 1 or True.
And doing nothing for if not found. Click OK.
Double Click to copy that down and you see the people with just a single name - it continues to work. We can also solve this with the If-not-found.
So here an extra comma and then in the If_Not_Found says “hey if there was no space just give us the entire cell”.
Double click and copy that down, so that's another way to work around. And then the question: why give us both of these?
Why give us match end and if not found?
There might be a time, I would guess, where when there is no space we would want to report an error.
So here instead of repeating A6, something simple like there isn't a space.
And then for all those people with just a single name and no delimiter, we can report that.
Thanks to the calc team in Redmond for giving us this great improvements.
It makes TEXTBEFORE and TEXTAFTER match with how Power Query handles TEXTBEFORE and TEXTAFTER.
And gives us just a few extra options for how to deal with those situations where the delimiter is not found. Hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
If you like these videos, please, down below, Like, Subscribe, and Ring the bell.
Feel free to post any questions or comments down in the comments below.
Now if you love Excel, check out my new courses on the Retrieve platform.
They are video courses but you just type what you're looking for.
It takes you right to that spot in the video and there's a complete transcript in several languages. It 's a super fast way to learn.
