How to Understand Excel Text Formulas
Bill Jellen: Hey I’m Bill Jellen from MysteryExcel.com and I’ve got a cool Excel tip for you today.
Mike Gel Girvin: Hey this is Mike Gel Girvin and an Excel is Fun in YouTube and I have different way to do that.
Dueling Excel
Bill Jellen: Hey that’s another dueling Excel podcast. I’m Bill Jellen we’ll have Mike Girvin from Excel is fun joining us and Mike, send this question in and someone send it to him. I’m sorry I don’t know who signed in the question, Mike maybe you can tell us and look at this we have all of this text out of here in Column A and someone wants to evaluate all this stuff but you know what VBA that’s the way to go. Alt F11 Insert, Modules, Sub EvalThem. All right 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 some more (0, 1).Formula= “=”&.value. So we build a formula that takes where’s 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 we’re done and just basically takes all that stuff, puts an equal sign before fast way to go. All right Mike what have we got.
Mike Gel Girvin: Thanks Mr. Excel VBA that’s the way to go. One of these days we have to get around the learning 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 this old Macro functions by using define the names. I’m going to click in cell B8 because my formula is going to have to have a relative cell reference using the defined name and when you do a relative cell reference for define names your cursor were ever you click determines where you going to click to tell the name manager that you’re doing a relative cell reference.
I’m going to use the keyboard shortcut control 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 going to type my formula. I’m trying that evaluate, open parenthesis and now we need a relative cell reference. Watch what happens when you click in the name manager, create or create name. It puts the sheet name and the exclamation point and then an absolute cell reference. Just hit the F4 key three times and then you got to get rid off all of that sheet reference stuff right there including the little apostrophe by putting in 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 explanation point, it says one to my left, there relative cell reference and that will work, it will work globally anywhere in our workbook, click okay, click close.
Now let’s try =text and there’s our name Control 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 a five times five and then I’m going to put equals TextMath and sure enough that’s a name with the define formula it will calculate. Now another way to go is if you have the more funks Excel adds in. There are 66 functions and if you watch that video actually how it were it get it, how to download it and how to install it, you’re just going to use the EVAL. EVAL is a function that comes and then evaluates a 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. Control F11. There is this Macro 2 this is not a regular worksheet, this will understand the old Macro functions. I’m going to type equals evaluate, open parenthesis 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 this sheets by default then show formulas. So you have to use tilt, which is to the left of the number one, Control tilt to get back to evaluate. Now, I probably would never use that, that’s just a weird bazaar trick to see that there’s this sheet that’s like from Excel 4. All right we’ll see —
Bill Jellen: Mike that’s brilliant using the old Excel 4 evaluate in a name range, I’d love that one. Don’t learn VBA but now yeah you should VBA but certainly it solves this problem. Hey, I want to thank everyone for stopping by see you next time for another dueling Excel podcast from Mr. Excel and Excel is fun.
Transcription by:
Scribe4you Transcription Services