Today's duel involves evaluating math stored inside a text cell such as "1.35x2MVA+3x1.35MVA". Bill and Mike head off in different directions, with Bill doing a VBA macro to extract the digits from the cell and Mike using an Excel 4 Macro function to EVALUATE. Also - learn why Ctrl+F11 inserts a Macro sheet and how you can use the macro sheet to test your EVALUATE function.
Transcript of the video:
Hey, welcome back it's time for another dueling Excel podcast.
I'm Bill Jelen from MrExcel will be joined by Mike Girvin from Excel Is Fun.
This is our episode 135.
Do the Math in the Text.
All right, this is a crazy questions in it from YouTube, we actually have text here and we want to generate the the result of that.
So, 1 times 3.15 will be 3.15, but 2 times 3.15 you know so this is going to be a disaster because you have a lot of things you have to ignore like the unit's here and even being able to do 1 times 5 plus 2 times 3.15 and have all of that calculate like this over here.
All right! So, my solution is going to be a VBA, Macro.
Of course we're going to select the cells we want to convert make sure that cells the right are completely empty.
We'll do Alt+F11 insert a module, Let's create a quick little model here called Foo, for each cell in selection we're going to start with a new variable called new formula and that's going to be equal to an equal sign we're going to take the text from cell.value.
We're going to loop for each character from one to the length of my text extract that character using the MID function just like in Excel we would use the MID function and then we're going to see what the character is if it's a digit 0 through 9 a plus a minus a divided or a period, then we're going to append that character onto the end of new formula.
If it's an X which they mean to be multiplication, then we're going to append an asterisk and we'll just go through each character, each character, each character see if it's an M or a V or any of those other letters it's just going to ignore it it's not going to do anything.
So, what we're going to end up with right before we write this formula So, back to the spreadsheet we're going to put just the, just the Math symbols.
So, here let's try it will run down to this point I'm going to hover over new formulas see we get equals 1 times 3.15.
I'm going to put that in the cell one column to the right of the cell.
All right, we'll get rid of that break point.
Now, we'll let the whole thing run and when we switch back to excel you'll see that we have not just the answer but we also have the real math behind the answer up in the formula bar.
There you go.
All right, Mike let's see what you have.
Mike: Thanks MrExcel.
Hey, gotta love that VBA, it was quick and easy and even left the formula with the math numbers and the operators.
Oh! man, this is a pretty wild and I actually do not know how to do it in a single formula I'm going to take it in two steps the first thing is I want to transform these get rid of any of those letters change the X to an asterisk.
So, I'm going to use this SUBSTITUTE function.
Now, we'll take this text and the first thing we'll do we will remove the old text MVA and the new text will be an old text string double quote, double quote that's the syntax for show nothing.
So, ready control+enter and there we got ridof the MVA with the column highlighted in the active cell.
I'll hit F2 and now, hey i'm going to use a second substitute.
It will take that as the text, I'm going to find in double quotes the x and put in its place substituting it in an asterisks, control+enter to populate that edited formula into the column.
Now, that's text and here again I have speak cells on enter, but that's text right what are we going to do with that.
Well, we're going to jump way back in history, we're going to look at an Excel for Macro function.
Now, we can use a defined name and if you know what those functions are from way back in history you can just use them in a defined name and actually you can search the Excel Is Fun channel for Excel for Macro Function.
I have a few videos on these, but you know i don't use them very often and I always forget.
So, there's a great trick.
Now, before we see this trick that will help us with excel for Macro functions.
What's the keyboard shortcut for inserting a new sheet, it's shift+F11.
So, shift+F11 there's a new sheet, but what do, I do i usually am such a bad typer, I accidentally hit control+F11 and what does control+F11 do, for those of you who have done this by mistake in like what's this Macro sheet.
Well, it's not a normal sheet, shift+F11 gave us a normal sheet, contol+F11 gives us a macro sheet, but here's the thing we can test Excel for Macro functions on a Macro sheet and they'll actually work.
So, I'm going to come over here copy come over and how about paste special values and let's try it there's a Macro for function, called Evaluate, open parentheses and when I control+enter.
Oh! Wait a second maybe it's not a real function no, but wait a second because it was all capitalized you know it is and here's the thing about Macro sheets when you insert them they get inserted in audit mode.
So, if you go up to formulas, show formulas you can toggle out or there's a toggle its control+tilde.
Now, lots of us use control+tilde because it works on regular sheets.
We could see the result of the formulas or the number formatting when we control+tilde, it shows us the formulas and the un-formatted number.
So, that's a toggle you can go back and forth and sure enough evaluate is working.
All right, let's go back because what we want to do is use a define name and create a universal relative cell reference and here's how it works if our defined name is always going to look to the left and do something on that cell you have to build your define name with an actual cell selected and we're going to click there and it will be a universal relative cell reference.
So, I'm gonna use the keyboard control+F3 to open up names, new, I'm going to call this evaluate, actually just the text evaluate and then I'm going to come down here.
Now, here's the thing you have to put B12.
Now, watch what happens when I click, it puts in a sheet name an explanation point, that explanation point is the syntax for sheet reference and then an absolute cell reference, but that's not what we want, you want to just put a single explanation point and then it is B So, this cell is selected C12 and I want to type out B12 and that's how we create a universal relative cell reference that means wherever we put this defined name, Evaluate it will always look to the left and try and evaluate it.
So, I click OK, click Close and let's try it, equals evaluate and you can see down here.
There it is it.
It's just a defined name control+enter and copy it down you've got to be kidding me look at that if we were to you know, come over here and do two carat 3 x minus 2 and that's text right come over here use our evaluate and no problem it will calculate the answer.
All right, we'll throw it back to MrExcel.
Bill: Hey, all right like that was awesome Excel for Macros, I never knew the control+F11 what a great trick I'm coming out to your channel right now clicking this search and do Excel for macro and we'll see what else is out there I gotta watch these to see what else we can do, These an amazing packers.
Oh! Hey, I wanna thank everyone for stopping by.
We'll see you next time or another dueling Excel podcast from MrExcel and Excel Is Fun.
I'm Bill Jelen from MrExcel will be joined by Mike Girvin from Excel Is Fun.
This is our episode 135.
Do the Math in the Text.
All right, this is a crazy questions in it from YouTube, we actually have text here and we want to generate the the result of that.
So, 1 times 3.15 will be 3.15, but 2 times 3.15 you know so this is going to be a disaster because you have a lot of things you have to ignore like the unit's here and even being able to do 1 times 5 plus 2 times 3.15 and have all of that calculate like this over here.
All right! So, my solution is going to be a VBA, Macro.
Of course we're going to select the cells we want to convert make sure that cells the right are completely empty.
We'll do Alt+F11 insert a module, Let's create a quick little model here called Foo, for each cell in selection we're going to start with a new variable called new formula and that's going to be equal to an equal sign we're going to take the text from cell.value.
We're going to loop for each character from one to the length of my text extract that character using the MID function just like in Excel we would use the MID function and then we're going to see what the character is if it's a digit 0 through 9 a plus a minus a divided or a period, then we're going to append that character onto the end of new formula.
If it's an X which they mean to be multiplication, then we're going to append an asterisk and we'll just go through each character, each character, each character see if it's an M or a V or any of those other letters it's just going to ignore it it's not going to do anything.
So, what we're going to end up with right before we write this formula So, back to the spreadsheet we're going to put just the, just the Math symbols.
So, here let's try it will run down to this point I'm going to hover over new formulas see we get equals 1 times 3.15.
I'm going to put that in the cell one column to the right of the cell.
All right, we'll get rid of that break point.
Now, we'll let the whole thing run and when we switch back to excel you'll see that we have not just the answer but we also have the real math behind the answer up in the formula bar.
There you go.
All right, Mike let's see what you have.
Mike: Thanks MrExcel.
Hey, gotta love that VBA, it was quick and easy and even left the formula with the math numbers and the operators.
Oh! man, this is a pretty wild and I actually do not know how to do it in a single formula I'm going to take it in two steps the first thing is I want to transform these get rid of any of those letters change the X to an asterisk.
So, I'm going to use this SUBSTITUTE function.
Now, we'll take this text and the first thing we'll do we will remove the old text MVA and the new text will be an old text string double quote, double quote that's the syntax for show nothing.
So, ready control+enter and there we got ridof the MVA with the column highlighted in the active cell.
I'll hit F2 and now, hey i'm going to use a second substitute.
It will take that as the text, I'm going to find in double quotes the x and put in its place substituting it in an asterisks, control+enter to populate that edited formula into the column.
Now, that's text and here again I have speak cells on enter, but that's text right what are we going to do with that.
Well, we're going to jump way back in history, we're going to look at an Excel for Macro function.
Now, we can use a defined name and if you know what those functions are from way back in history you can just use them in a defined name and actually you can search the Excel Is Fun channel for Excel for Macro Function.
I have a few videos on these, but you know i don't use them very often and I always forget.
So, there's a great trick.
Now, before we see this trick that will help us with excel for Macro functions.
What's the keyboard shortcut for inserting a new sheet, it's shift+F11.
So, shift+F11 there's a new sheet, but what do, I do i usually am such a bad typer, I accidentally hit control+F11 and what does control+F11 do, for those of you who have done this by mistake in like what's this Macro sheet.
Well, it's not a normal sheet, shift+F11 gave us a normal sheet, contol+F11 gives us a macro sheet, but here's the thing we can test Excel for Macro functions on a Macro sheet and they'll actually work.
So, I'm going to come over here copy come over and how about paste special values and let's try it there's a Macro for function, called Evaluate, open parentheses and when I control+enter.
Oh! Wait a second maybe it's not a real function no, but wait a second because it was all capitalized you know it is and here's the thing about Macro sheets when you insert them they get inserted in audit mode.
So, if you go up to formulas, show formulas you can toggle out or there's a toggle its control+tilde.
Now, lots of us use control+tilde because it works on regular sheets.
We could see the result of the formulas or the number formatting when we control+tilde, it shows us the formulas and the un-formatted number.
So, that's a toggle you can go back and forth and sure enough evaluate is working.
All right, let's go back because what we want to do is use a define name and create a universal relative cell reference and here's how it works if our defined name is always going to look to the left and do something on that cell you have to build your define name with an actual cell selected and we're going to click there and it will be a universal relative cell reference.
So, I'm gonna use the keyboard control+F3 to open up names, new, I'm going to call this evaluate, actually just the text evaluate and then I'm going to come down here.
Now, here's the thing you have to put B12.
Now, watch what happens when I click, it puts in a sheet name an explanation point, that explanation point is the syntax for sheet reference and then an absolute cell reference, but that's not what we want, you want to just put a single explanation point and then it is B So, this cell is selected C12 and I want to type out B12 and that's how we create a universal relative cell reference that means wherever we put this defined name, Evaluate it will always look to the left and try and evaluate it.
So, I click OK, click Close and let's try it, equals evaluate and you can see down here.
There it is it.
It's just a defined name control+enter and copy it down you've got to be kidding me look at that if we were to you know, come over here and do two carat 3 x minus 2 and that's text right come over here use our evaluate and no problem it will calculate the answer.
All right, we'll throw it back to MrExcel.
Bill: Hey, all right like that was awesome Excel for Macros, I never knew the control+F11 what a great trick I'm coming out to your channel right now clicking this search and do Excel for macro and we'll see what else is out there I gotta watch these to see what else we can do, These an amazing packers.
Oh! Hey, I wanna thank everyone for stopping by.
We'll see you next time or another dueling Excel podcast from MrExcel and Excel Is Fun.
