Today's Excel question from Willie at the UCF Accounting Conference:
I am using a vlookup to get some information from a vendor info database that comes from the credit card website.
My issue is that the vendor name sometimes comes with additional info at the end like …. “Contoso - cadet.biz” and in the table where I do the lookup the name is just “Contoso”.
I was wondering if you think I can use the “TEXTBEFORE” function with the Vlookup formula to make the formula to look in the table only for the first word of the info coming from the credit card “Contoso - cadet.biz”.
Table of Contents
(0:00) UCF Accounting Show
(0:39) Table with Vendor dash URL
(1:15) Use TEXTBEFORE with VLOOKUP
(1:50) Use TEXTBEFORE, XLOOKUP, for lookup table
(2:50) TEXTBEFORE preventing #N/A
(5:09) Prevent #N/A from TEXTBEFORE in lookup table
(6:55) Check out my Excel video courses on Retrieve
I am using a vlookup to get some information from a vendor info database that comes from the credit card website.
My issue is that the vendor name sometimes comes with additional info at the end like …. “Contoso - cadet.biz” and in the table where I do the lookup the name is just “Contoso”.
I was wondering if you think I can use the “TEXTBEFORE” function with the Vlookup formula to make the formula to look in the table only for the first word of the info coming from the credit card “Contoso - cadet.biz”.
Table of Contents
(0:00) UCF Accounting Show
(0:39) Table with Vendor dash URL
(1:15) Use TEXTBEFORE with VLOOKUP
(1:50) Use TEXTBEFORE, XLOOKUP, for lookup table
(2:50) TEXTBEFORE preventing #N/A
(5:09) Prevent #N/A from TEXTBEFORE in lookup table
(6:55) Check out my Excel video courses on Retrieve
Transcript of the video:
Bill Jelen: Well hey, yesterday, Thursday.
I was up at the University of Central Florida for their annual Accounting Conference.
In the past couple of years, it's been a virtual conference, but they were back live. Oh, it was so great to be in front of a live audience again showing people what's new in Excel because when you're there live, you can see people react.
"Oh yeah, that's a great idea".
This question today is from Willie, who was at the conference.
Willie has a VLOOKUP problem and he saw the TEXTBEFORE function, which is an insider's fast right now.
He's like, "Oh, I wonder if that would solve my problem".
And at the time I said, "Oh, it should be simple". But it turns out it's not as simple as I thought.
Let's take a look. All right.
So Willie has a vendor database that he's downloading that has the name of the vendor, a space, a dash, a space, and then some sort of a URL.
And he wondered if he could use TEXTBEFORE to get everything before the dash.
And I thought, yep, that should work. And at first I had the wrong idea.
I thought that the data that he was downloading was over here, where he had to do the VLOOKUP from.
And so you have a lookup table over here that just says, Bellows College.
And over here, you might say Bellows College space, dash, space, and then some URL out there.
So in that case, it was pretty simple.
We're going to do a VLOOKUP instead of looking up A2, we do the TEXTBEFORE and now you have to be really careful here because it's space, dash, and we don't want to insert a trailing space, which you could easily do if you just did TEXTBEFORE the dash.
So I wanted do TEXTBEFORE in quotes space, dash, and then the rest of the VLOOKUP stays the same from M to O.
We want the second column and a zero or false at the end for an exact match and double click to copy that down.
But then when I reread Willie's question, I realized it's the lookup table that has the extra data.
So over here in his regular data, he just has the company name or the vendor name.
But over here in the lookup table, he's downloading this from, he said a credit card app.
It has the vendor, space, dash, space, and then the URL. All right.
So now this is going to force us to switch from VLOOKUP to XLOOKUP, but that's okay.
So the formula that I've written here, XLOOKUP, we're going to look up that company name and then where are we going to look?
Well we're going to do the TEXTBEFORE of that entire column?
M2 to M41, looking for everything before the space, dash, and then in this case, I'm going to return the limit, which is over here in O and that worked great. I'm like, "oh, yeah, this is going to be perfect.
You're going to love it". As soon as you can get your company to upgrade you to insiders fast.
So you have the new TEXTBEFORE function.
But then as I was preparing to record this video, I always copy the question and put it here on the question sheet.
And one word caught me.
He says, my issue is that the vendor name sometimes comes with additional info at the end, like Contoso, space, dash, space, Contoso.biz sometimes. Oh, well, that complicates things dramatically.
All right. So here's the same formula.
The first formula where we have the extra dash over here in column A and as I copied that down, I started to get a lot of NAs and the NAs are happening where we don't have a dash.
And unfortunately that's a problem with TEXTBEFORE.
I hope before it gets to general availability that Joe McDaid and the Excel team decides to change this.
But right now, if we're looking for TEXTBEFORE the dash, and there is no dash, they return the NA error.
I would think it would make more sense just return the entire text.
No dash there just give me the whole text.
So it's very tempting because we're getting an NA error to deal with this using if NA, so if we get an NA, then give me A2, otherwise give me the TEXTBEORE A2, everything before this space, dash.
But I'm going to propose something different and it's going to become really important in the next example, we're doing the XLOOKUP and doing a TEXTBEFORE on many items.
I'm going to propose using TEXTBEFORE A2 ampersand, space, dash.
So what's that going to do here with Lamina Healthcare Company, I'm going to get Lamina Healthcare Company, space, dash, space, worry.com, space, dash.
But it doesn't matter because the TEXTBEFORE is going to get rid of everything before the dash.
But here in Graphic Design Institute, where there is no space, dash, I'm going to force a space, dash to be there. And then the TEXTBEFORE will get rid of it.
And that little change would allow me to prevent the whole NA so A2, ampersand, quote, space, dash, end quote, like that. Double click and copy it down.
All right.
Now, in reality, either of these would be okay here. There's no issue.
The problem though is when we have to do the XLOOKUP.
So with the XLOOKUP, we're going to get errors when there's not a dash over here.
Like all these ones in red are going to generate NA errors.
And I think it's easier with the TEXTBEFORE to say, I'm going to take all of these items in M2 to M41.
I'm going to concatenate them, ampersand with space, dash, end quote, and then it starts to work.
All right.
So at first it seemed really simple, but there's things that will be the gotchas.
The other gotcha that I worried about is I used to have a data set where Roto-Rooter with a dash, R O T O dash Rooter was in the data.
And that dash could be a problem.
But when I went back and looked, it wasn't Roto space dash, it was just dash. So I escaped that bullet.
Still you need to be careful in case some of the vendors for some weird reason already have a dash.
And then the credit card company is adding a second space, dash, space.
Then you might have to handle those in some other way.
I made a big deal back in 2019 that I wasn't going to be traveling anymore and getting off the road, University of Central Florida.
My new favorite football team, they are just 48 minutes from my house. I made a joke.
I'll do any live seminars as long as it's one that 49 minute drive.
It was so nice.
No airplanes and boy, it was just great to be back in front of a live audience.
I have several podcasts lined up with ideas or questions that came from this live event.
Thanks to professor Sean, Dennis for organizing this and inviting me.
I had just a great time there at the University of Central Florida.
Well, Hey, I want to thank you for stopping by.
We'll see you next time for another night cast for Mr. Excel.
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're 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.
I was up at the University of Central Florida for their annual Accounting Conference.
In the past couple of years, it's been a virtual conference, but they were back live. Oh, it was so great to be in front of a live audience again showing people what's new in Excel because when you're there live, you can see people react.
"Oh yeah, that's a great idea".
This question today is from Willie, who was at the conference.
Willie has a VLOOKUP problem and he saw the TEXTBEFORE function, which is an insider's fast right now.
He's like, "Oh, I wonder if that would solve my problem".
And at the time I said, "Oh, it should be simple". But it turns out it's not as simple as I thought.
Let's take a look. All right.
So Willie has a vendor database that he's downloading that has the name of the vendor, a space, a dash, a space, and then some sort of a URL.
And he wondered if he could use TEXTBEFORE to get everything before the dash.
And I thought, yep, that should work. And at first I had the wrong idea.
I thought that the data that he was downloading was over here, where he had to do the VLOOKUP from.
And so you have a lookup table over here that just says, Bellows College.
And over here, you might say Bellows College space, dash, space, and then some URL out there.
So in that case, it was pretty simple.
We're going to do a VLOOKUP instead of looking up A2, we do the TEXTBEFORE and now you have to be really careful here because it's space, dash, and we don't want to insert a trailing space, which you could easily do if you just did TEXTBEFORE the dash.
So I wanted do TEXTBEFORE in quotes space, dash, and then the rest of the VLOOKUP stays the same from M to O.
We want the second column and a zero or false at the end for an exact match and double click to copy that down.
But then when I reread Willie's question, I realized it's the lookup table that has the extra data.
So over here in his regular data, he just has the company name or the vendor name.
But over here in the lookup table, he's downloading this from, he said a credit card app.
It has the vendor, space, dash, space, and then the URL. All right.
So now this is going to force us to switch from VLOOKUP to XLOOKUP, but that's okay.
So the formula that I've written here, XLOOKUP, we're going to look up that company name and then where are we going to look?
Well we're going to do the TEXTBEFORE of that entire column?
M2 to M41, looking for everything before the space, dash, and then in this case, I'm going to return the limit, which is over here in O and that worked great. I'm like, "oh, yeah, this is going to be perfect.
You're going to love it". As soon as you can get your company to upgrade you to insiders fast.
So you have the new TEXTBEFORE function.
But then as I was preparing to record this video, I always copy the question and put it here on the question sheet.
And one word caught me.
He says, my issue is that the vendor name sometimes comes with additional info at the end, like Contoso, space, dash, space, Contoso.biz sometimes. Oh, well, that complicates things dramatically.
All right. So here's the same formula.
The first formula where we have the extra dash over here in column A and as I copied that down, I started to get a lot of NAs and the NAs are happening where we don't have a dash.
And unfortunately that's a problem with TEXTBEFORE.
I hope before it gets to general availability that Joe McDaid and the Excel team decides to change this.
But right now, if we're looking for TEXTBEFORE the dash, and there is no dash, they return the NA error.
I would think it would make more sense just return the entire text.
No dash there just give me the whole text.
So it's very tempting because we're getting an NA error to deal with this using if NA, so if we get an NA, then give me A2, otherwise give me the TEXTBEORE A2, everything before this space, dash.
But I'm going to propose something different and it's going to become really important in the next example, we're doing the XLOOKUP and doing a TEXTBEFORE on many items.
I'm going to propose using TEXTBEFORE A2 ampersand, space, dash.
So what's that going to do here with Lamina Healthcare Company, I'm going to get Lamina Healthcare Company, space, dash, space, worry.com, space, dash.
But it doesn't matter because the TEXTBEFORE is going to get rid of everything before the dash.
But here in Graphic Design Institute, where there is no space, dash, I'm going to force a space, dash to be there. And then the TEXTBEFORE will get rid of it.
And that little change would allow me to prevent the whole NA so A2, ampersand, quote, space, dash, end quote, like that. Double click and copy it down.
All right.
Now, in reality, either of these would be okay here. There's no issue.
The problem though is when we have to do the XLOOKUP.
So with the XLOOKUP, we're going to get errors when there's not a dash over here.
Like all these ones in red are going to generate NA errors.
And I think it's easier with the TEXTBEFORE to say, I'm going to take all of these items in M2 to M41.
I'm going to concatenate them, ampersand with space, dash, end quote, and then it starts to work.
All right.
So at first it seemed really simple, but there's things that will be the gotchas.
The other gotcha that I worried about is I used to have a data set where Roto-Rooter with a dash, R O T O dash Rooter was in the data.
And that dash could be a problem.
But when I went back and looked, it wasn't Roto space dash, it was just dash. So I escaped that bullet.
Still you need to be careful in case some of the vendors for some weird reason already have a dash.
And then the credit card company is adding a second space, dash, space.
Then you might have to handle those in some other way.
I made a big deal back in 2019 that I wasn't going to be traveling anymore and getting off the road, University of Central Florida.
My new favorite football team, they are just 48 minutes from my house. I made a joke.
I'll do any live seminars as long as it's one that 49 minute drive.
It was so nice.
No airplanes and boy, it was just great to be back in front of a live audience.
I have several podcasts lined up with ideas or questions that came from this live event.
Thanks to professor Sean, Dennis for organizing this and inviting me.
I had just a great time there at the University of Central Florida.
Well, Hey, I want to thank you for stopping by.
We'll see you next time for another night cast for Mr. Excel.
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're 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.
