Today's Dueling Excel comes from France... Mike "ExcelisFun" Girvin and Bill "MrExcel" Jelen are looking for the words 'Top laod' or 'Front load', and then Cubic Feet. also need to change the decimal to a comma to make the value appropriate in French.
Using Formulas, a little VBA and some creativity, Mike and Bill offer a few different solutions to the challenge in Episode #1492.
Using Formulas, a little VBA and some creativity, Mike and Bill offer a few different solutions to the challenge in Episode #1492.
Transcript of the video:
Bill: Hey. Welcome back. It’s another Dueling Excel podcast.
I'm Bill Jelen from MrExcel. I’ll be joined by Mike Girvin from ExcelIsFun.
This is episode 94, find cubic feet.
Hey, Mike. I got a good one for you today. This was sent in from France.
They have a list of product descriptions over here, and 2 questions, looking for either the word TOP LOAD or FRONT LOAD based on what's found, and then looking for cubic feet and going back to get the value, and just to confuse things, this person's from France.
They need to take the 2.6 and make it 2,6.
Alright.
Okay.
First one.
I think solving at the FRONT LOAD is kind of straightforward.
We look for the FIND of TOP LOAD in the lower of this and it's going to return a value when FRONT LOAD is found.
When it's not found, we're going to get the VALUE error.
So, I'm going to say =IF ISERROR, that whole thing, if that’s an error, then it’s not a front loader.
So, we're going to put nothing, otherwise we're going to put FRONT LOAD, and I'm actually going to take that whole big IF statement, concatenate a second IF statement with…have a look.
So, the simple concept, do the find.
The find is either going to return a value, a number, or an error.
Check for the error.
If it's the error, then it's not a TOP LOAD, otherwise it is, and then repeat the same thing, concatenate that whole thing together, and we have our answer.
[ =IF(ISERROR(FIND(“top load”,LOWER(A3))),“”,“Top Load”)&IF(ISERROR(FIND(“front load”,LOWER(A3))),“”,“Front Load”) ] The second one though, the second one, is the one that just makes my head hurt, and so, anytime that my head hurts, I instantly think VBA.
VBA is the way to go.
Let's go out to VBA and we're going to create our own little custom function here.
I called it CF for cubic feet.
We're going to pass it a value, start out by initializing that variable, and then I'm going to go through and look character by character all the way through my value, and see if, starting at that character position, so we’re going to start looking at character 1 for length of 5, does it say cubic feet?
Look at 2 for a length of 5, does it say cubic feet?
Eventually, you know, out of row position 17 where you get to cubic feet, and then, from there, I'm going to start from that position 17 and go backwards, go backwards, and start looking at the characters.
If it's a space, I'm not going to include it but I'm going to keep going.
If it's a . then I'm going to concatenate a , with what's already in cubic feet.
If it's any digit, then I'm going to concatenate that digit with what's in cubic feet.
If it's anything else, a letter, anything, then I know that I'm done and I found the end of the whole thing, so I just exit the function, alright?
Now, to make this work, we’re going to say =CF, that whole big string over there.
Okay.
Now, so, I'm getting an answer here where it has cu.ft but not an answer there written as cu. space ft or cu ft.
That's really, really, really, really, really, really, really, really bad.
So, now, this would be a little bit dangerous.
You're going to have to spot check this.
We're just going to look for cu and hope that there's not a model called the [ cu – 03:10 ] model or something like that.
So, let's go back and re-enter these.
There we go and now it starts to work.
Here where there's no cubic feet at all, we get nothing.
I don’t like mine.
Mine's a little dangerous.
Mike, let's see what you have.
[ =CF(A3) ] Mike: Thanks, MrExcel.
Dangerous.
If yours is dangerous, mine’s going to be double dangerous, and, really, here's the problem.
We're trying to get that number or that number or that number from a text string where we really need some sort of consistent pattern, but here we have cu space after the number.
Right here, we have space cu . space.
Down here, we have space cu . no space, and before, we have 24 inches space.
Here we have , space.
So, I'm not quite sure how to do this except for look for cu.
So, in the second part here, I'm going to look for space cu.
Now, the first part is, in essence, we're looking up TOP LOAD and FRONT LOAD within a text string, and they need to either list TOP LOAD or FRONT LOAD.
Well, instead of find, I'm going to use search, and the difference between find and search is search is not case-sensitive at all, so it can handle wildcards which is not applicable here.
So, I’m going to use FIND text and the text I'm going to look for is TOP LOAD within that right there.
Now, it's going to do the same thing.
Let’s see.
As soon as you see a cell reference is not color coded, like it should be blue, you know there's a problem with your formula.
So, that'll do the same thing as find.
It tells us the position where it finds the T in TOP LOAD, 26.
Now I'm not really looking for TOP LOAD.
I'm looking for TOP LOAD or front loader.
So, I'm going to put this in curly brackets, which is array syntax.
So, I'll have 2 items, TOP LOAD , or FRONT LOAD.
Now, by putting it in these curly brackets, that's an array constant.
So, now, if I highlight this and hit the F9 key, the find text is expecting a single text string.
We're giving it 2 so it will return 2 things.
So, I'm creating a little array with either a number or an error.
So, the only 2 possibilities are if there's never going to be TOP LOAD and FRONT LOAD together, it's number error or number error.
I'm going to CONTROL+Z.
Now, what am I going to do with that?
That, in essence, is going to be the trigger to either dump TOP LOAD or FRONT LOAD into this cell.
I'm going to use the LOOKUP function.
Now, the great thing about LOOKUP is we can give it some LOOKUP VALUE.
It has a LOOKUP VECTOR which is the trigger to then say what you should choose from the RESULT VECTOR.
So, what am I going to look up?
Well, this is text, the largest number of characters in a cell, 2^15, 2 raised to the 15th power – 1.
So, my LOOKUP VALUE, I just want to leave that big number in there.
That way, it will always find either number here or here.
So, LOOKUP VALUE, the LOOKUP VECTOR, which is the trigger to then , list my RESULT VECTOR, right?
So, then I'm going to just paste that.
Oops.
I did that front load…instead of front, it should be FRONT LOAD.
Alright.
So, then, I can ), CONTROL+ENTER, and then double-click and send it down.
So, yeah.
Okay.
So, that's working.
[ unintelligible – 06:50 ] fix the front part, FRONT LOAD.
Alright.
Now, what about those N/As?
We can simply, in 2007 and ’10, use IFERROR.
What's great about this is you list the function once, and then you come to the end and tell it what to put in the cell if it's an error.
I’m going to put “” which means a null text string.
It'll show nothing in our result, formula result.
Perfect.
Now, in earlier versions, you'd have to use IF and ISERROR and then list the LOOKUP twice.
[ =IFERROR(LOOKUP(2^15,SEARCH({“Top Load”,“Front Load”},A3),{“Top Load”,“Front Load”}),“”) ] Alright.
Now to the second part.
I'm going to search for the space cu.
Now, I also like this search function because watch this.
=s searches the first function in the Ss, so I can just type =s TAB.
I'm going to look for space cu , within.
Now this is also going to give me a position but I'm going to use that 8, right?
That's that position right there.
I'm going to use it inside the MID function.
The MID function can extract some subtext within a larger text string.
So, there's the text , the starting position is not 8.
It's going to be 8–4.
Now, I'm thinking there's the 8, so 1, 2, 3, and 4 just in case there's a 1.
Now, sometimes that will deliver an extra space but that won't be a problem.
So, starting position 8-4, and number of characters, 4.
Now, that's giving us space and MID always spits out text, right, but there's an extra space there.
Now, I kind of like that 2.6.
Maybe they do want a , but if you want just the 2.6, you could use the TRIM, right?
TRIM is great.
It gives you a haircut.
No, no.
I mean it puts you on a diet.
No, no.
It just gets rid of spaces, right?
Ah, but check this out.
If you really did want 2.6, you could simply [ undid – 08:48 ] there, add 0.
Now, converting a number stored as text… a number stored as text back to a number, you do any math operation.
So, I'm going to add 0 but the add 0 will get rid of the space also.
Now, if, in fact, you didn't want that 2.6, we'd have to not use that.
I would use TRIM and that gets rid of the space, and then SUBSITUTE, and simply say, hey, substitute.
There's the text , this text we want to substitute is find that . in “ and put in in “ a , and then, of course, you could…looks like it's working all the way down.
You could do your IFERROR or IF ISERROR.
[ =SUBSTITUTE(TRIM(MID(A3,SEARCH(“ cu”,A3)-4,4)),“.”,“,”) ] Alright.
I’ll throw it back to MrExcel.
Bill: Hey.
Alright.
That's good.
TRIM.
Next time I need to go on a diet, I'll just use the TRIM function.
Alright.
Well, I want to thank everyone for stopping by.
We'll see you next time 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 episode 94, find cubic feet.
Hey, Mike. I got a good one for you today. This was sent in from France.
They have a list of product descriptions over here, and 2 questions, looking for either the word TOP LOAD or FRONT LOAD based on what's found, and then looking for cubic feet and going back to get the value, and just to confuse things, this person's from France.
They need to take the 2.6 and make it 2,6.
Alright.
Okay.
First one.
I think solving at the FRONT LOAD is kind of straightforward.
We look for the FIND of TOP LOAD in the lower of this and it's going to return a value when FRONT LOAD is found.
When it's not found, we're going to get the VALUE error.
So, I'm going to say =IF ISERROR, that whole thing, if that’s an error, then it’s not a front loader.
So, we're going to put nothing, otherwise we're going to put FRONT LOAD, and I'm actually going to take that whole big IF statement, concatenate a second IF statement with…have a look.
So, the simple concept, do the find.
The find is either going to return a value, a number, or an error.
Check for the error.
If it's the error, then it's not a TOP LOAD, otherwise it is, and then repeat the same thing, concatenate that whole thing together, and we have our answer.
[ =IF(ISERROR(FIND(“top load”,LOWER(A3))),“”,“Top Load”)&IF(ISERROR(FIND(“front load”,LOWER(A3))),“”,“Front Load”) ] The second one though, the second one, is the one that just makes my head hurt, and so, anytime that my head hurts, I instantly think VBA.
VBA is the way to go.
Let's go out to VBA and we're going to create our own little custom function here.
I called it CF for cubic feet.
We're going to pass it a value, start out by initializing that variable, and then I'm going to go through and look character by character all the way through my value, and see if, starting at that character position, so we’re going to start looking at character 1 for length of 5, does it say cubic feet?
Look at 2 for a length of 5, does it say cubic feet?
Eventually, you know, out of row position 17 where you get to cubic feet, and then, from there, I'm going to start from that position 17 and go backwards, go backwards, and start looking at the characters.
If it's a space, I'm not going to include it but I'm going to keep going.
If it's a . then I'm going to concatenate a , with what's already in cubic feet.
If it's any digit, then I'm going to concatenate that digit with what's in cubic feet.
If it's anything else, a letter, anything, then I know that I'm done and I found the end of the whole thing, so I just exit the function, alright?
Now, to make this work, we’re going to say =CF, that whole big string over there.
Okay.
Now, so, I'm getting an answer here where it has cu.ft but not an answer there written as cu. space ft or cu ft.
That's really, really, really, really, really, really, really, really bad.
So, now, this would be a little bit dangerous.
You're going to have to spot check this.
We're just going to look for cu and hope that there's not a model called the [ cu – 03:10 ] model or something like that.
So, let's go back and re-enter these.
There we go and now it starts to work.
Here where there's no cubic feet at all, we get nothing.
I don’t like mine.
Mine's a little dangerous.
Mike, let's see what you have.
[ =CF(A3) ] Mike: Thanks, MrExcel.
Dangerous.
If yours is dangerous, mine’s going to be double dangerous, and, really, here's the problem.
We're trying to get that number or that number or that number from a text string where we really need some sort of consistent pattern, but here we have cu space after the number.
Right here, we have space cu . space.
Down here, we have space cu . no space, and before, we have 24 inches space.
Here we have , space.
So, I'm not quite sure how to do this except for look for cu.
So, in the second part here, I'm going to look for space cu.
Now, the first part is, in essence, we're looking up TOP LOAD and FRONT LOAD within a text string, and they need to either list TOP LOAD or FRONT LOAD.
Well, instead of find, I'm going to use search, and the difference between find and search is search is not case-sensitive at all, so it can handle wildcards which is not applicable here.
So, I’m going to use FIND text and the text I'm going to look for is TOP LOAD within that right there.
Now, it's going to do the same thing.
Let’s see.
As soon as you see a cell reference is not color coded, like it should be blue, you know there's a problem with your formula.
So, that'll do the same thing as find.
It tells us the position where it finds the T in TOP LOAD, 26.
Now I'm not really looking for TOP LOAD.
I'm looking for TOP LOAD or front loader.
So, I'm going to put this in curly brackets, which is array syntax.
So, I'll have 2 items, TOP LOAD , or FRONT LOAD.
Now, by putting it in these curly brackets, that's an array constant.
So, now, if I highlight this and hit the F9 key, the find text is expecting a single text string.
We're giving it 2 so it will return 2 things.
So, I'm creating a little array with either a number or an error.
So, the only 2 possibilities are if there's never going to be TOP LOAD and FRONT LOAD together, it's number error or number error.
I'm going to CONTROL+Z.
Now, what am I going to do with that?
That, in essence, is going to be the trigger to either dump TOP LOAD or FRONT LOAD into this cell.
I'm going to use the LOOKUP function.
Now, the great thing about LOOKUP is we can give it some LOOKUP VALUE.
It has a LOOKUP VECTOR which is the trigger to then say what you should choose from the RESULT VECTOR.
So, what am I going to look up?
Well, this is text, the largest number of characters in a cell, 2^15, 2 raised to the 15th power – 1.
So, my LOOKUP VALUE, I just want to leave that big number in there.
That way, it will always find either number here or here.
So, LOOKUP VALUE, the LOOKUP VECTOR, which is the trigger to then , list my RESULT VECTOR, right?
So, then I'm going to just paste that.
Oops.
I did that front load…instead of front, it should be FRONT LOAD.
Alright.
So, then, I can ), CONTROL+ENTER, and then double-click and send it down.
So, yeah.
Okay.
So, that's working.
[ unintelligible – 06:50 ] fix the front part, FRONT LOAD.
Alright.
Now, what about those N/As?
We can simply, in 2007 and ’10, use IFERROR.
What's great about this is you list the function once, and then you come to the end and tell it what to put in the cell if it's an error.
I’m going to put “” which means a null text string.
It'll show nothing in our result, formula result.
Perfect.
Now, in earlier versions, you'd have to use IF and ISERROR and then list the LOOKUP twice.
[ =IFERROR(LOOKUP(2^15,SEARCH({“Top Load”,“Front Load”},A3),{“Top Load”,“Front Load”}),“”) ] Alright.
Now to the second part.
I'm going to search for the space cu.
Now, I also like this search function because watch this.
=s searches the first function in the Ss, so I can just type =s TAB.
I'm going to look for space cu , within.
Now this is also going to give me a position but I'm going to use that 8, right?
That's that position right there.
I'm going to use it inside the MID function.
The MID function can extract some subtext within a larger text string.
So, there's the text , the starting position is not 8.
It's going to be 8–4.
Now, I'm thinking there's the 8, so 1, 2, 3, and 4 just in case there's a 1.
Now, sometimes that will deliver an extra space but that won't be a problem.
So, starting position 8-4, and number of characters, 4.
Now, that's giving us space and MID always spits out text, right, but there's an extra space there.
Now, I kind of like that 2.6.
Maybe they do want a , but if you want just the 2.6, you could use the TRIM, right?
TRIM is great.
It gives you a haircut.
No, no.
I mean it puts you on a diet.
No, no.
It just gets rid of spaces, right?
Ah, but check this out.
If you really did want 2.6, you could simply [ undid – 08:48 ] there, add 0.
Now, converting a number stored as text… a number stored as text back to a number, you do any math operation.
So, I'm going to add 0 but the add 0 will get rid of the space also.
Now, if, in fact, you didn't want that 2.6, we'd have to not use that.
I would use TRIM and that gets rid of the space, and then SUBSITUTE, and simply say, hey, substitute.
There's the text , this text we want to substitute is find that . in “ and put in in “ a , and then, of course, you could…looks like it's working all the way down.
You could do your IFERROR or IF ISERROR.
[ =SUBSTITUTE(TRIM(MID(A3,SEARCH(“ cu”,A3)-4,4)),“.”,“,”) ] Alright.
I’ll throw it back to MrExcel.
Bill: Hey.
Alright.
That's good.
TRIM.
Next time I need to go on a diet, I'll just use the TRIM function.
Alright.
Well, I want to thank everyone for stopping by.
We'll see you next time for another Dueling Excel podcast from MrExcel and ExcelIsFun.
