Someone put a bunch of mathematical calculations in column A as text and we want to evaluate those formulas. Mike and Bill provide dueling ways to solve this problem in Episode 1149.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Bill: Hey, I'm Bill Jelen from MrExcel.com, and I've got a cool Excel tip for you today!
Mike: Hey this is Mike Gel Girvin, I’m ExcelIsFun in YouTube, and I have a different way to do that!
Bill: Hey alright, it's another Dueling Excel podcast, I'm Bill Jelen, we'll have Mike Girvin from ExcelIsFun joining us.
And Mike sent this question, and someone sent it in to him, I'm sorry, I don't know who's sending the question Mike, maybe you can tell us.
And look at this, we have all of this text out here in column A, and someone wants to evaluate all this stuff.
Well, you know what, VBA, that's the way to go, Alt+F11, Insert, Module, Sub EvalThem, alright, here we go.
For Each cell In selection cell.Offset, Offset, says “Hey, we're not going to use the cell, we're going to go somewhere,” zero rows down one column over, .Formula = , check this out, “=” & cell.Value . So we build a formula that takes whatever is over there in column A puts an equal sign before it, Next cell , sweet!
Come back here, choose our values, Alt+F8 to get a list of macros, EvalThem, Run, BAM, we're done!
And it just basically takes all that stuff, puts an equal sign before, fast way to go.
Alright Mike, what do you got?
Mike: Thanks MrExcel!
VBA, that's the way to go!
One of these days I have to get around to learn VBA.
In the meantime I'm going to have to use the “EVALUATE” function, but wait a second, there is no evaluate function, yes.
But there was in Excel 4, an old macro function, and you can access these old macro functions by using defined names.
I'm going to click in cell B8, because my formula is going to have to have a relative cell reference using a defined name.
And when you do a relative cell reference, your cursor, wherever you click, determines where you're going to click to tell the Name Manager that you're doing a relative cell reference.
I’m going to use the keyboard shortcut Ctrl+F3 to open the Name Manager, and then click New, I'm going to call this name TextMath.
Now I'm going to click down here and I'm going to type my formula, I'm trying to access that evaluate function, =EVALUATE( , and now we need a relative cell reference.
Watch what happens when you click, in the Name Manager it puts the sheet name, and an exclamation point, and an absolute cell reference.
Just hit the F4 key 3 times, and then you got to get rid of all of that sheet reference stuff right there, including the little apostrophe.
By putting an exclamation point and the cell reference, and by the way, this name box knows contextually, it knows that you've selected B8.
So the fact that we put A8, it doesn't really see A8 when you put this exclamation point, it says one to my left, a relative cell reference, and that will work in, or globally anywhere in our workbook, click OK, click Close.
Now let's try it, =EVALUATE, oh I'm sorry, =Text- and there's our name, Ctrl+Enter.
So a name has a formula, and it is calculating one cell to my left using that old EVALUATE function from Excel 4.
Now let's just go over here and try 5*5, and then I'm going to put =TextMath, and sure enough, that’s a name with a defined formula, it will calculate now.
Another way to go is if you have the MoreFunc Excel add-in, there's 66 functions, and if you watch that video, I show you where to get it, how to download it, and how to install it, you can just use the EVAL!
EVAL is a function that comes and it evaluates text, just like that.
Still another way to do it is, if you don't want to do define names, watch this, there's a keyboard shortcut to get back to Excel 4!
You can insert a macro sheet that understands the EVALUATE function, Ctrl+F11, and there it says, Macro2, this is not a regular worksheet, this will understand the old macro functions.
I'm going to type =EVALUATE( , and I'm going to click on my cell reference right there, and Enter- Oh, but wait a second, it's not working!
That's because these sheets, by default, are in Show Formulas.
So you have to use `, which is to the left of the number 1, Ctrl+`, to get back to EVALUATE.
Now I probably would never use that, that’s just a weird bizarre trick to see that “There's this sheet that's like from Excel 4?” Alright, we'll see you- Bill: Mike that's brilliant!
Using the old Excel 4 EVALUATE in a named range, I love that one, don't learn VBA!
Well, yeah, you should learn VBA, but certainly solves this problem.
Hey, I want to thank everyone for stopping by, see you next time for another Dueling Excel podcast from MrExcel and ExcelIsFun!
Mike: Hey this is Mike Gel Girvin, I’m ExcelIsFun in YouTube, and I have a different way to do that!
Bill: Hey alright, it's another Dueling Excel podcast, I'm Bill Jelen, we'll have Mike Girvin from ExcelIsFun joining us.
And Mike sent this question, and someone sent it in to him, I'm sorry, I don't know who's sending the question Mike, maybe you can tell us.
And look at this, we have all of this text out here in column A, and someone wants to evaluate all this stuff.
Well, you know what, VBA, that's the way to go, Alt+F11, Insert, Module, Sub EvalThem, alright, here we go.
For Each cell In selection cell.Offset, Offset, says “Hey, we're not going to use the cell, we're going to go somewhere,” zero rows down one column over, .Formula = , check this out, “=” & cell.Value . So we build a formula that takes whatever is over there in column A puts an equal sign before it, Next cell , sweet!
Come back here, choose our values, Alt+F8 to get a list of macros, EvalThem, Run, BAM, we're done!
And it just basically takes all that stuff, puts an equal sign before, fast way to go.
Alright Mike, what do you got?
Mike: Thanks MrExcel!
VBA, that's the way to go!
One of these days I have to get around to learn VBA.
In the meantime I'm going to have to use the “EVALUATE” function, but wait a second, there is no evaluate function, yes.
But there was in Excel 4, an old macro function, and you can access these old macro functions by using defined names.
I'm going to click in cell B8, because my formula is going to have to have a relative cell reference using a defined name.
And when you do a relative cell reference, your cursor, wherever you click, determines where you're going to click to tell the Name Manager that you're doing a relative cell reference.
I’m going to use the keyboard shortcut Ctrl+F3 to open the Name Manager, and then click New, I'm going to call this name TextMath.
Now I'm going to click down here and I'm going to type my formula, I'm trying to access that evaluate function, =EVALUATE( , and now we need a relative cell reference.
Watch what happens when you click, in the Name Manager it puts the sheet name, and an exclamation point, and an absolute cell reference.
Just hit the F4 key 3 times, and then you got to get rid of all of that sheet reference stuff right there, including the little apostrophe.
By putting an exclamation point and the cell reference, and by the way, this name box knows contextually, it knows that you've selected B8.
So the fact that we put A8, it doesn't really see A8 when you put this exclamation point, it says one to my left, a relative cell reference, and that will work in, or globally anywhere in our workbook, click OK, click Close.
Now let's try it, =EVALUATE, oh I'm sorry, =Text- and there's our name, Ctrl+Enter.
So a name has a formula, and it is calculating one cell to my left using that old EVALUATE function from Excel 4.
Now let's just go over here and try 5*5, and then I'm going to put =TextMath, and sure enough, that’s a name with a defined formula, it will calculate now.
Another way to go is if you have the MoreFunc Excel add-in, there's 66 functions, and if you watch that video, I show you where to get it, how to download it, and how to install it, you can just use the EVAL!
EVAL is a function that comes and it evaluates text, just like that.
Still another way to do it is, if you don't want to do define names, watch this, there's a keyboard shortcut to get back to Excel 4!
You can insert a macro sheet that understands the EVALUATE function, Ctrl+F11, and there it says, Macro2, this is not a regular worksheet, this will understand the old macro functions.
I'm going to type =EVALUATE( , and I'm going to click on my cell reference right there, and Enter- Oh, but wait a second, it's not working!
That's because these sheets, by default, are in Show Formulas.
So you have to use `, which is to the left of the number 1, Ctrl+`, to get back to EVALUATE.
Now I probably would never use that, that’s just a weird bizarre trick to see that “There's this sheet that's like from Excel 4?” Alright, we'll see you- Bill: Mike that's brilliant!
Using the old Excel 4 EVALUATE in a named range, I love that one, don't learn VBA!
Well, yeah, you should learn VBA, but certainly solves this problem.
Hey, I want to thank everyone for stopping by, see you next time for another Dueling Excel podcast from MrExcel and ExcelIsFun!
