In this week's dueling Excel Mike and Bill take a look at ways to take an address and get just the state. Bill's Method uses a VBA and Mike takes the formula route. Visit MrExcel.com - Your One Stop for Excel Tips and Solutions.
Transcript of the video:
Hey, welcome back. It's another dueling Excel podcast episode number 53.
I'm Bill Jelen from MrExcel.
We will be joined by Mike Girvin from Excel Is Fun.
This episode is called Extract State Doozy of a question here, they have this long address.
They want to get just the state from there.
And the problem is like here where we had an extra comma, it was returning CH from Charleston instead of the state, right?
So I'm sure that Mike is going to come up with a formula for this, but me, I am switching over to VBA and we'll knock out a quick little function VBA, just a way to do this without making my head hurt.
So hold F11, insert a module, create a new function.
Call a function, which is called GetState(MyText).
All right, here's what we're going to do.
We're gonna say for I = Len (MyText) to 1 Step - 1.
See that? That's going to go backwards through each character in my text and we are going to start checking to see what it is.
If Mid (MyText, I, 1) = "," Then I know that I found the last comma, and we're gonna assign the result to a variable called GetState That variable has the same name as the function.
And that's how that gets returned back to the spreadsheet = Mid(MyText, I+2, 2).
So, we're at that comma, we want to skip the comma, we want to skip the space, go grab the next two and actually once we find it, we don't need to loop through and look for more results, so we can simply Exit For, End If, Next I.
All right, sure it's good, debug, compile.
So, now to use this will come back to excel and what we have to do is =GetSate(A2) Is SC for South Carolina?
Copy that down, Illinois, Nevada and even works... there with Charleston.
All right.
Works with only one column like through that one in.
So there you have it.
A VBA solution.
Certainly, there's a formula that can solve this, but a little bit of VBA if you like to code.
Just makes the whole problem go away.
Mike. Let's see what you have.
Mike: Hey, thanks MrExcel.
Oh, you gotta love that VBA stepping backwards through a text string.
I wish I could do that with the right function.
Hey, GetState, that's totally awesome.
I'll do it the long way.
I'll use a formula.
Now, when I look at this, I see 2 commas here and I want the last comma right down here.
There's one, one two three, so I always need the last comma, even that when there's one, so I'm gonna try that but the first thing, I need to do if I'm going to find a last comma, I need to extract all the individual characters here, so I'm going to try the =MID( And MID can extract a single piece of text substring from a larger text on and say hey, I want to extract something from there, but here's the trick, the starting number...
I need to have a starting number of, you know, if there's forty characters here I need to have 40 starting numbers, so I'm gonna do =MID(A2,{1,2,3,4,5,6,7},1) All right? Just to see how this works, so that'll attract this...
extract the first seven characters and then the number of characters to extract each time is one.
So then if I highlight this and hit the F9 key, you can see sure enough.
It extracted the first seven characters, CTRL+Z, the only trick is how are we going to get a variable array?
This is an array constant with 1 to 40 or 1 to 18 down here.
I am gonna start by looking at the =INDIRECT( and I can build...
Actually no, I'm going to start off by looking at the LEN.
The LEN can give me the length.
Right so, 44 and as I copy this down, you know, this will tell me the ending character so, I just need to add something to the beginning to say 1 to 44 1 to 34, so I'm going to use the INDIRECT function.
=INDIRECT("1:"&LEN(A2) so, right now if I highlight this if I hit the F9 key, I have 1 to 44.That'll give me the 1 to 44, 1 to 37, 1 to 38 as I copy it down.
The INDIRECT will convert that text string to a reference and then the ROW function wrapped around that will give me an array with the numbers 1 to 44.
So, if I hit F9 I can see and that will change as we go down so CTRL+ Z.
Now, I can put that inside of the MID of this.
This is creating an array because it's returning lots more than one thing, so I hit F9 and you can can see there's all the characters, CTRL+ Z.
Now, I need to say hey anything in there equal to in quotes comma.
Right, so now this is going to create an array of trues and falses.
CTRL+Z. Now, I'm going to put that array in parentheses.
and then say =1/(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)=",") Now, what does that do 1 divided by?
Well, I want to do the 1 divided by the last, that comparative operator there if I didn't put parentheses would get calculated lasts so I want it To calculate before that division so boom Put this inside of parentheses.
I divide all those trues and falses by one and I'm simply going to get F9 a bunch of divided by zeros and one you can see there's a one there and a one there boom I have my ones every time there's a comma CTRL+Z now I can use =MATCH(2,1/MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)) Match and Match is great because it will look up and tell you the position of an item look up I'm just going to pick some number bigger than one that'll always be better the only thing that's ever going to pop up here is 1 so I'm going to type a 2 for the Lookup that's the array and the thing here is that if you don't put comma Exact you can use the default and it will do an approximate match and so we'll keep hunting For a to not find it and when it finds that last number one, it'll take that now.
Let's go ahead and F9 this you can see gives me 30 And that is the exact position here of the last comma.
I'm going to CTRL+Z that's um The starting position so, now I'm going to use mid =MID(A2,MATCH(2,1/MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))=","))+2,2) comma and then I want to extract well 30 will give me The comma right and really want to go to the next space and then the next letter, so I'm going to actually for the match thing here the starting number I'm going to add 2 comma and then I want 2 which is the length of the state now This is an array. So I have to hold CTRL+SHIFT and ENTER You could see the curly brackets right there double click and send it down.
It looks like I got it now if If we look at this a little bit closer this thing right here that one divided of I broke.
That whole thing is the part that's delivering an array I'm gonna CTRL+X to cut it and just like in last week's duel when I had an array I went ahead and put it inside the Index.
=MID(A2,MATCH(2,INDEX(1/MID(A2ROW(INDIERCT("1:"&LEN(A2))),1)=','=) ,)+2,2 And so I'm kind of confused here.
I don't know where I am, but I'm going to look at my screen tips array I'm going to type a comma and if you put a row number it It'll extract you know the third one or the fourth on, but if you leave it blank I just put that comma it'll extract all the rows.
So, close parenthesis, and that's it enter. Yes See, so if I click back up here no curly brackets.
I'll double click and send it down All right Definitely, more convoluting complex than that VBA but that's the best. I can do here all right. I'll throw back over to MrExcel Excel: Hey, Mike, that was a wild wild formula.
I'm gonna stick with VBA for everyone else.
Take your pick when I thank you for stopping by, I will see you next time for another dueling excel podcast from Mr.Excel and Excel Is Fun.
I'm Bill Jelen from MrExcel.
We will be joined by Mike Girvin from Excel Is Fun.
This episode is called Extract State Doozy of a question here, they have this long address.
They want to get just the state from there.
And the problem is like here where we had an extra comma, it was returning CH from Charleston instead of the state, right?
So I'm sure that Mike is going to come up with a formula for this, but me, I am switching over to VBA and we'll knock out a quick little function VBA, just a way to do this without making my head hurt.
So hold F11, insert a module, create a new function.
Call a function, which is called GetState(MyText).
All right, here's what we're going to do.
We're gonna say for I = Len (MyText) to 1 Step - 1.
See that? That's going to go backwards through each character in my text and we are going to start checking to see what it is.
If Mid (MyText, I, 1) = "," Then I know that I found the last comma, and we're gonna assign the result to a variable called GetState That variable has the same name as the function.
And that's how that gets returned back to the spreadsheet = Mid(MyText, I+2, 2).
So, we're at that comma, we want to skip the comma, we want to skip the space, go grab the next two and actually once we find it, we don't need to loop through and look for more results, so we can simply Exit For, End If, Next I.
All right, sure it's good, debug, compile.
So, now to use this will come back to excel and what we have to do is =GetSate(A2) Is SC for South Carolina?
Copy that down, Illinois, Nevada and even works... there with Charleston.
All right.
Works with only one column like through that one in.
So there you have it.
A VBA solution.
Certainly, there's a formula that can solve this, but a little bit of VBA if you like to code.
Just makes the whole problem go away.
Mike. Let's see what you have.
Mike: Hey, thanks MrExcel.
Oh, you gotta love that VBA stepping backwards through a text string.
I wish I could do that with the right function.
Hey, GetState, that's totally awesome.
I'll do it the long way.
I'll use a formula.
Now, when I look at this, I see 2 commas here and I want the last comma right down here.
There's one, one two three, so I always need the last comma, even that when there's one, so I'm gonna try that but the first thing, I need to do if I'm going to find a last comma, I need to extract all the individual characters here, so I'm going to try the =MID( And MID can extract a single piece of text substring from a larger text on and say hey, I want to extract something from there, but here's the trick, the starting number...
I need to have a starting number of, you know, if there's forty characters here I need to have 40 starting numbers, so I'm gonna do =MID(A2,{1,2,3,4,5,6,7},1) All right? Just to see how this works, so that'll attract this...
extract the first seven characters and then the number of characters to extract each time is one.
So then if I highlight this and hit the F9 key, you can see sure enough.
It extracted the first seven characters, CTRL+Z, the only trick is how are we going to get a variable array?
This is an array constant with 1 to 40 or 1 to 18 down here.
I am gonna start by looking at the =INDIRECT( and I can build...
Actually no, I'm going to start off by looking at the LEN.
The LEN can give me the length.
Right so, 44 and as I copy this down, you know, this will tell me the ending character so, I just need to add something to the beginning to say 1 to 44 1 to 34, so I'm going to use the INDIRECT function.
=INDIRECT("1:"&LEN(A2) so, right now if I highlight this if I hit the F9 key, I have 1 to 44.That'll give me the 1 to 44, 1 to 37, 1 to 38 as I copy it down.
The INDIRECT will convert that text string to a reference and then the ROW function wrapped around that will give me an array with the numbers 1 to 44.
So, if I hit F9 I can see and that will change as we go down so CTRL+ Z.
Now, I can put that inside of the MID of this.
This is creating an array because it's returning lots more than one thing, so I hit F9 and you can can see there's all the characters, CTRL+ Z.
Now, I need to say hey anything in there equal to in quotes comma.
Right, so now this is going to create an array of trues and falses.
CTRL+Z. Now, I'm going to put that array in parentheses.
and then say =1/(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)=",") Now, what does that do 1 divided by?
Well, I want to do the 1 divided by the last, that comparative operator there if I didn't put parentheses would get calculated lasts so I want it To calculate before that division so boom Put this inside of parentheses.
I divide all those trues and falses by one and I'm simply going to get F9 a bunch of divided by zeros and one you can see there's a one there and a one there boom I have my ones every time there's a comma CTRL+Z now I can use =MATCH(2,1/MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)) Match and Match is great because it will look up and tell you the position of an item look up I'm just going to pick some number bigger than one that'll always be better the only thing that's ever going to pop up here is 1 so I'm going to type a 2 for the Lookup that's the array and the thing here is that if you don't put comma Exact you can use the default and it will do an approximate match and so we'll keep hunting For a to not find it and when it finds that last number one, it'll take that now.
Let's go ahead and F9 this you can see gives me 30 And that is the exact position here of the last comma.
I'm going to CTRL+Z that's um The starting position so, now I'm going to use mid =MID(A2,MATCH(2,1/MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))=","))+2,2) comma and then I want to extract well 30 will give me The comma right and really want to go to the next space and then the next letter, so I'm going to actually for the match thing here the starting number I'm going to add 2 comma and then I want 2 which is the length of the state now This is an array. So I have to hold CTRL+SHIFT and ENTER You could see the curly brackets right there double click and send it down.
It looks like I got it now if If we look at this a little bit closer this thing right here that one divided of I broke.
That whole thing is the part that's delivering an array I'm gonna CTRL+X to cut it and just like in last week's duel when I had an array I went ahead and put it inside the Index.
=MID(A2,MATCH(2,INDEX(1/MID(A2ROW(INDIERCT("1:"&LEN(A2))),1)=','=) ,)+2,2 And so I'm kind of confused here.
I don't know where I am, but I'm going to look at my screen tips array I'm going to type a comma and if you put a row number it It'll extract you know the third one or the fourth on, but if you leave it blank I just put that comma it'll extract all the rows.
So, close parenthesis, and that's it enter. Yes See, so if I click back up here no curly brackets.
I'll double click and send it down All right Definitely, more convoluting complex than that VBA but that's the best. I can do here all right. I'll throw back over to MrExcel Excel: Hey, Mike, that was a wild wild formula.
I'm gonna stick with VBA for everyone else.
Take your pick when I thank you for stopping by, I will see you next time for another dueling excel podcast from Mr.Excel and Excel Is Fun.
