One cell has a security name, ticker symbol, then price. The complicated part is that the security name might have 3, 4, or 5 words. How can you extract the last 2 words into new cells and everything else as the security name? Bill and Mike try Flash Fill, a VBA function, and a long formula to solve the problem.
Transcript of the video:
Bill: Hey. Welcome back. It’s time for another Dueling Excel podcast.
I'm Bill Jelen from MrExcel. I’ll be joined by Mike Girvin from ExcelIsFun.
This is our episode 140, stock symbol and price or extract the last two words.
Hey, Mike. I got a doozy for you.
This question came in from YouTube and she had actually seen one of our old dueling podcasts where we split words and numbers out, but there was an X in there.
She said, well, wait a second.
What if you don't have an X, and this is a really tough one because, as I look at this, we're trying to get the price which is the number at the end, and then the symbol which is the word before that, and then everything else goes back into FUND NAME, and even FLASH FILL has trouble with this.
So, if I type the first one and then start to type the second one, just I, see, they're getting it wrong.
They’re including too much a lot of times.
So, I'm going to not accept that, and edit, and now that we have the two, let's see if that's enough for FLASH FILL to figure it out.
ISHARES RUSSELL 2000, missed the 2000, fix that one.
There we go.
So, finally after typing the two and then correcting the one, it looks like FLASH FILL is getting the FUND NAME correct, but let's see, with the symbol, that's working great.
The numbers though it, I haven't figured it out anyway.
I've tried a whole bunch of different things.
765, 764.58.
I've tried it with the ,. I've tried it without the ,. I know the FLASH FILL does not like to use numbers.
Like, you have to ask them to go try and do FLASH FILL, and see, there, they’re missing the 2.
So, if I fill that one in, yeah, so it's like [ unintelligible – 1:40 ] is thinking the , there is important and then it goes haywire after that.
Let’s see if we give it an example of 3 and try that, if they can figure it out.
Yeah.
Again, okay, so, it's really a tough data set, but she says she does not have Excel 2013.
She's all the way back on Excel 2003.
So, at this point, of course, I'm going to switch over to VBA.
With VBA, it's going to be fairly simple to attack this.
I'm going to figure out the length of the cell, and then I'm going to start from the right edge and go backwards.
I’m going to ignore anything until I get back to the space.
Once I see the space, I know that, at that point, I have the price, and then everything to the left of it is the description and the symbol, and then, again, I'm going to start going backwards character by character until I find the space and I know that that's where the data will go.
Now, this macro that I wrote is based on the selection.
I'm assuming that the 3 columns to the right are blank.
So, we’ll do ALT+F11 and here we go.
BREAKTHEMOUT.
FOR EACH CELL IN SELECTION, grab the cell value, and I actually…the first time I wrote this, it would be much faster, now that I start to think about it.
I'm going from the end of the value TO 1 STEP -1, take a look at the character using the MID function, and if it's 1 through 0, a , or a ., I ignore it.
I don't do anything.
Otherwise, I assume I've gotten to that space or the non-breaking space or whatever they put in there and I'm good to go figuring out the price.
So, we start at the MID of MYVAL , i + 1.
I don't specify a length and, in VBA, that's nice.
You don't have to specify a length.
It just goes out to the end, and we get the price.
So, from the cell, we go out 3 columns, put the price in, and then change my value to be the leftmost characters right up to the [ unintelligible – 03:35 ] that space.
EXIT THE FOR.
We come down here to this next for.
Then, almost the exact same thing, we look for the character this time.
I'm just looking for the space, and when I find it, I know that I have the symbol, put that 2 cells to the right, and then everything to the left of that is assumed to be the stock name or description.
So, here, let's just try it.
We’ll press ALT+F8 and say break them out, click RUN, and perfect.
It gets everything without the hassle.
So, here, you know, FLASH FILL is really kind of fooled by this because it was a hard data set, but because the VBA was able to go from the end and go backwards, working through it, basically just pulling out the first or the last word, the next to the last word, and then everything else, it seemed to be easier.
Mike, let's see if you have a formula that would do this.
Mike: Thanks, MrExcel.
Oh man, I don't like formulas like this.
This is just flat-out a hard formula to extract these things because there's no consistent pattern.
Now, we'll come back to a formula in just a moment but, you know, FLASH FILL is absolutely amazing if you have 2013 or later.
The trick is, though, you have to know your data.
If you don't know your whole column data and all the patterns and intricacies of the data, then FLASH FILL can get into trouble, but as I look here, I see one word, two word, three word, and second to last space.
I come down here, I see one word, two word, second to last space, one, two, three, four before we get to the second to the last space.
So, if we give it those three examples, we know for sure, you know, and I did, like, a formula for a huge column, and then looked at the max of these to try and find a pattern.
Of course, by the time you do those formulas, maybe it's just as easy to do a formula, but let's try this.
This is good to know about FLASH FILL that…SPDR.
So, we have one example with three bits of text separated by a space.
I’m going to come down here.
POWERSHARES.
Oops, I better spell it right, and then QQQ, and then finally this one.
Now, it tries to FLASH FILL on these.
I'm going to come to the end and hit ENTER, and then, instead of coming up here, and you're using this a lot, use CONTROL+E for FLASH FILL.
So, we gave it the 3 examples and it got everything correct.
TICKER, that one's easy.
SPY, CONTROL+ENTER, CONTROL+E.
Now, the numbers here, in my experience, the numbers get into trouble when you have 0s, like dates, 07 for the month or times or pennies like this, because really we want to say, go from the last space and extract all the characters.
So, I'm going to come down here.
I'm going to try and give it two examples here.
4 , 764.58 and then I'm going to come all the way down to the bottom or search until you find one with the 01, and I'm going to say 484.01, CONTROL+ENTER, CONTROL+E, and so that will get it.
0 is causing trouble with numbers sometimes.
Alright.
Now, a formula.
I just don't know any good fast way to do this.
The only way I know how to deal with this with variable number spaces before you want to get the last one is we have to insert a character here.
So, watch this.
First, we're going to have to count how many spaces there are.
Say, the LEN of all those and then the LEN of, and we're going to use this substitute.
There's no SUN but there is a substitute function.
The text, , the old text is “ space ”. I'm looking for the spaces and now I want to put new text “” that says put nothing.
So, then, I'm counting that.
So, it'll count the thing with all the spaces and then subtract the ones without them, and, there, it tells us what the position of the last space is.
Not the position.
That means that's how many spaces there are.
Now, I'm going to have to insert a character there.
So, I'm going to have to do SUBSTITUTE again.
No SUN.
The text is going to be this , the old text I’m looking for is a space, and “. I'm going to have to pick some text that's not in the text string ever, so I'm going to put that, and that's the instance.
So, now, I've created a text string with a little character there.
Now, I have to search for that to figure out the position.
So, I'm going to search for ^ within that right there.
Oh, this is just ridiculous.
Alright.
So, now, I know the position of that, and I can use the replace, and I'm going to say REPLACE.
The old text is this , the start number.
REPLACE goes from a certain number character to another number character and replaces something.
So, I’m going to say start at 1, go all the way to that number of characters, and the new text I want is “.Now, there's a few different ways we do that but I tend to use REPLACE.
[ =REPLACE(A2,1,SEARCH(“^”,SUBSTITUTE(A2,“ ”,“^”,LEN(A2)-LEN(SUBSTITUTE(A2,“ ”,“”)))),“”) ] That's just yucky.
Oh man.
It's text.
You can convert it to a number with 0.
I'm going to come over here.
I'm going to use that right there and use it to get the SPY now.
So, I'm going to say =SUB, not SUN.
I'm going to say, here's the text.
The old text is that right there.
Now, I want to get that space there as the lead.
So, I'm going to do space in “, &. That's the old text I want to substitute and now I'm going to put nothing in there, and so that will give me, if I look at this SPY, and now I can simply take the right, the right of that.
Whoops.
Right , 3.
[ =RIGHT(SUBSTITUTE(A2,“ ”&D2,“”),3) ] Oh, man, this is making my head hurt, all this, and now I want to get the FUND and I'm going to base it off of these.
So, I'm going to say =SUBSTITUTE again.
All these substitutes.
I'm going to take this, , and the old text I'm going to look for is that I want that space before the SPY.
So, I'm going to put a space &, join it to SPY, and then a space between this and that.
That little string right there will search for that and, of course, for when we find that, the new text is going to be nothing, CONTROL+ENTER.
[ =SUBSTITUTE(A2,“ ”&C2&“ ”&D2,“”) ] Oh man.
That is an absolute mess, and I'm sure there's a better way to do it, and I've done a number of formulas like this over the years and they always make my head hurt, but that's the only way I know is insert a little thing there and then there's these.
Alright.
FLASH FILL.
I wish FLASH FILL would be…you know, FLASH FILL is awesome.
It's only as good as our knowledge of the data set and our choice of examples.
Alright.
Throw it back to MrExcel.
Bill: Hey.
Alight.
Mike, that was really, really cool.
great tip there with FLASH FILL.
I've never seen filling in 3 examples before.
You do the FLASH FILL and then substitute one of those functions that I’D never think to use.
Great use there.
It'd be great if we had a function that was called reverse that would take a text string and reverse it.
Then, it would be easy to find the first space, second space, and it would be very predictable then to get the answer and reverse those again, but, unfortunately, we still do not have a reverse function.
Alright.
Well, hey.
I want to thank everyone for stopping by.
We’ll see you next week for another Dueling Excel podcast from MrExcel and ExcelIsFun.
I'm Bill Jelen from MrExcel. I’ll be joined by Mike Girvin from ExcelIsFun.
This is our episode 140, stock symbol and price or extract the last two words.
Hey, Mike. I got a doozy for you.
This question came in from YouTube and she had actually seen one of our old dueling podcasts where we split words and numbers out, but there was an X in there.
She said, well, wait a second.
What if you don't have an X, and this is a really tough one because, as I look at this, we're trying to get the price which is the number at the end, and then the symbol which is the word before that, and then everything else goes back into FUND NAME, and even FLASH FILL has trouble with this.
So, if I type the first one and then start to type the second one, just I, see, they're getting it wrong.
They’re including too much a lot of times.
So, I'm going to not accept that, and edit, and now that we have the two, let's see if that's enough for FLASH FILL to figure it out.
ISHARES RUSSELL 2000, missed the 2000, fix that one.
There we go.
So, finally after typing the two and then correcting the one, it looks like FLASH FILL is getting the FUND NAME correct, but let's see, with the symbol, that's working great.
The numbers though it, I haven't figured it out anyway.
I've tried a whole bunch of different things.
765, 764.58.
I've tried it with the ,. I've tried it without the ,. I know the FLASH FILL does not like to use numbers.
Like, you have to ask them to go try and do FLASH FILL, and see, there, they’re missing the 2.
So, if I fill that one in, yeah, so it's like [ unintelligible – 1:40 ] is thinking the , there is important and then it goes haywire after that.
Let’s see if we give it an example of 3 and try that, if they can figure it out.
Yeah.
Again, okay, so, it's really a tough data set, but she says she does not have Excel 2013.
She's all the way back on Excel 2003.
So, at this point, of course, I'm going to switch over to VBA.
With VBA, it's going to be fairly simple to attack this.
I'm going to figure out the length of the cell, and then I'm going to start from the right edge and go backwards.
I’m going to ignore anything until I get back to the space.
Once I see the space, I know that, at that point, I have the price, and then everything to the left of it is the description and the symbol, and then, again, I'm going to start going backwards character by character until I find the space and I know that that's where the data will go.
Now, this macro that I wrote is based on the selection.
I'm assuming that the 3 columns to the right are blank.
So, we’ll do ALT+F11 and here we go.
BREAKTHEMOUT.
FOR EACH CELL IN SELECTION, grab the cell value, and I actually…the first time I wrote this, it would be much faster, now that I start to think about it.
I'm going from the end of the value TO 1 STEP -1, take a look at the character using the MID function, and if it's 1 through 0, a , or a ., I ignore it.
I don't do anything.
Otherwise, I assume I've gotten to that space or the non-breaking space or whatever they put in there and I'm good to go figuring out the price.
So, we start at the MID of MYVAL , i + 1.
I don't specify a length and, in VBA, that's nice.
You don't have to specify a length.
It just goes out to the end, and we get the price.
So, from the cell, we go out 3 columns, put the price in, and then change my value to be the leftmost characters right up to the [ unintelligible – 03:35 ] that space.
EXIT THE FOR.
We come down here to this next for.
Then, almost the exact same thing, we look for the character this time.
I'm just looking for the space, and when I find it, I know that I have the symbol, put that 2 cells to the right, and then everything to the left of that is assumed to be the stock name or description.
So, here, let's just try it.
We’ll press ALT+F8 and say break them out, click RUN, and perfect.
It gets everything without the hassle.
So, here, you know, FLASH FILL is really kind of fooled by this because it was a hard data set, but because the VBA was able to go from the end and go backwards, working through it, basically just pulling out the first or the last word, the next to the last word, and then everything else, it seemed to be easier.
Mike, let's see if you have a formula that would do this.
Mike: Thanks, MrExcel.
Oh man, I don't like formulas like this.
This is just flat-out a hard formula to extract these things because there's no consistent pattern.
Now, we'll come back to a formula in just a moment but, you know, FLASH FILL is absolutely amazing if you have 2013 or later.
The trick is, though, you have to know your data.
If you don't know your whole column data and all the patterns and intricacies of the data, then FLASH FILL can get into trouble, but as I look here, I see one word, two word, three word, and second to last space.
I come down here, I see one word, two word, second to last space, one, two, three, four before we get to the second to the last space.
So, if we give it those three examples, we know for sure, you know, and I did, like, a formula for a huge column, and then looked at the max of these to try and find a pattern.
Of course, by the time you do those formulas, maybe it's just as easy to do a formula, but let's try this.
This is good to know about FLASH FILL that…SPDR.
So, we have one example with three bits of text separated by a space.
I’m going to come down here.
POWERSHARES.
Oops, I better spell it right, and then QQQ, and then finally this one.
Now, it tries to FLASH FILL on these.
I'm going to come to the end and hit ENTER, and then, instead of coming up here, and you're using this a lot, use CONTROL+E for FLASH FILL.
So, we gave it the 3 examples and it got everything correct.
TICKER, that one's easy.
SPY, CONTROL+ENTER, CONTROL+E.
Now, the numbers here, in my experience, the numbers get into trouble when you have 0s, like dates, 07 for the month or times or pennies like this, because really we want to say, go from the last space and extract all the characters.
So, I'm going to come down here.
I'm going to try and give it two examples here.
4 , 764.58 and then I'm going to come all the way down to the bottom or search until you find one with the 01, and I'm going to say 484.01, CONTROL+ENTER, CONTROL+E, and so that will get it.
0 is causing trouble with numbers sometimes.
Alright.
Now, a formula.
I just don't know any good fast way to do this.
The only way I know how to deal with this with variable number spaces before you want to get the last one is we have to insert a character here.
So, watch this.
First, we're going to have to count how many spaces there are.
Say, the LEN of all those and then the LEN of, and we're going to use this substitute.
There's no SUN but there is a substitute function.
The text, , the old text is “ space ”. I'm looking for the spaces and now I want to put new text “” that says put nothing.
So, then, I'm counting that.
So, it'll count the thing with all the spaces and then subtract the ones without them, and, there, it tells us what the position of the last space is.
Not the position.
That means that's how many spaces there are.
Now, I'm going to have to insert a character there.
So, I'm going to have to do SUBSTITUTE again.
No SUN.
The text is going to be this , the old text I’m looking for is a space, and “. I'm going to have to pick some text that's not in the text string ever, so I'm going to put that, and that's the instance.
So, now, I've created a text string with a little character there.
Now, I have to search for that to figure out the position.
So, I'm going to search for ^ within that right there.
Oh, this is just ridiculous.
Alright.
So, now, I know the position of that, and I can use the replace, and I'm going to say REPLACE.
The old text is this , the start number.
REPLACE goes from a certain number character to another number character and replaces something.
So, I’m going to say start at 1, go all the way to that number of characters, and the new text I want is “.Now, there's a few different ways we do that but I tend to use REPLACE.
[ =REPLACE(A2,1,SEARCH(“^”,SUBSTITUTE(A2,“ ”,“^”,LEN(A2)-LEN(SUBSTITUTE(A2,“ ”,“”)))),“”) ] That's just yucky.
Oh man.
It's text.
You can convert it to a number with 0.
I'm going to come over here.
I'm going to use that right there and use it to get the SPY now.
So, I'm going to say =SUB, not SUN.
I'm going to say, here's the text.
The old text is that right there.
Now, I want to get that space there as the lead.
So, I'm going to do space in “, &. That's the old text I want to substitute and now I'm going to put nothing in there, and so that will give me, if I look at this SPY, and now I can simply take the right, the right of that.
Whoops.
Right , 3.
[ =RIGHT(SUBSTITUTE(A2,“ ”&D2,“”),3) ] Oh, man, this is making my head hurt, all this, and now I want to get the FUND and I'm going to base it off of these.
So, I'm going to say =SUBSTITUTE again.
All these substitutes.
I'm going to take this, , and the old text I'm going to look for is that I want that space before the SPY.
So, I'm going to put a space &, join it to SPY, and then a space between this and that.
That little string right there will search for that and, of course, for when we find that, the new text is going to be nothing, CONTROL+ENTER.
[ =SUBSTITUTE(A2,“ ”&C2&“ ”&D2,“”) ] Oh man.
That is an absolute mess, and I'm sure there's a better way to do it, and I've done a number of formulas like this over the years and they always make my head hurt, but that's the only way I know is insert a little thing there and then there's these.
Alright.
FLASH FILL.
I wish FLASH FILL would be…you know, FLASH FILL is awesome.
It's only as good as our knowledge of the data set and our choice of examples.
Alright.
Throw it back to MrExcel.
Bill: Hey.
Alight.
Mike, that was really, really cool.
great tip there with FLASH FILL.
I've never seen filling in 3 examples before.
You do the FLASH FILL and then substitute one of those functions that I’D never think to use.
Great use there.
It'd be great if we had a function that was called reverse that would take a text string and reverse it.
Then, it would be easy to find the first space, second space, and it would be very predictable then to get the answer and reverse those again, but, unfortunately, we still do not have a reverse function.
Alright.
Well, hey.
I want to thank everyone for stopping by.
We’ll see you next week for another Dueling Excel podcast from MrExcel and ExcelIsFun.
