Bill was conducting a seminar yesterday and was asked to wrap a Formula around existing Formulas. Today, in Episode #1705, Bill addresses this question and shows us what can be done to accomplish the task.
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
This MrExcel podcast is sponsored by Easy Excel. Learn Excel from MrExcel podcast, episode 1705.
wrap every formula in a new formula. Hey, welcome back to the MrExcel net cast. I'm Bill Jelen.
Yesterday.
I was over in Perrysburg Ohio doing a seminar and Tory, right at the end of the seminar, asked a question that was very interesting tutorials as a whole bunch of different formulas, different formulas, right?
And she wants to select the range and wrap a new function around all of these formulas.
Like for example, maybe we want to add equal round after the equal sign and then comma zero at the end. Well, that's a tough, tough thing to do.
You know, I initially said, well, maybe, maybe what we could do is we could do, um, uh, find, replace and change every occurrence of the equal sign to a, for example, carrot equal sign that will make it not be formula anymore.
Uh, so now that they're not forming those, then we can kind of do things in a yeah. Couple of steps.
So we can take the equal sign and change it to equal round, open parentheses, replace all, uh, and then hope that there's only one parentheses in the formula. And boy, that's just a dangerous thing right there.
Uh, and that replaced all.
Uh, and then, find then once all that's done, we can change the carrot equals back to N equals and replace all click, okay, close, we'll get out of show form of this mode.
And now everything, uh, all of the formulas here have around, around them.
Um, but that wouldn't work if there was any other right parentheses in the range.
Like even these formulas up here that ran formulas is if we change the rate parentheses to write comma zero, that formula just goes bad.
So maybe the way to go, I'm going to switch over to VBA alt F 11, uh, selected, uh, here, insert module, and then a tiny little macro, uh, add route for each cell in selection.
Old formulas equals two cell dot formula.
New formula is equal to a, we start out with the literal equal round open parentheses, and then the mid of the old format starting with two now, mid and VBA is a lot like mid in Excel.
Uh, but we just have to say, we don't have to specify a length. We can just say it starts at two.
If you don't specify how long it goes all the way to the end, why don't we start a two that's because I want to get rid of the opening equal sign?
And then at the end, rapid in comma zero compresses, zero cell formula is equal to new formula. Uh, so let's come back here.
I'm going to select the whole range, uh, all F8 all F8 and then run the macro. And, uh, it adds the round function around the whole thing.
This could in theory, be work, uh, you know, could do other things, for example, all of 11. So we want to have the old formula and then maybe wrap everything in the proper function.
All of that doesn't make a lot of sense today. Uh, we need to proper everything.
And so just click run and see.
Now we've added the, uh, proper to all of these, of course, that are other formulas go bad, just because I changed the numbers to text.
But you could use this method with a little bit of VBA to add just about any kind of our wrapper function around all of the formulas and arrange. It's certainly an interesting question.
If you're watching this on YouTube and you have a better idea of how to do this, please feel free to leave us a comment.
Uh, you know, uh, as I said, it was Tory's question right at the end of the seminar, I'm like, Oh, I'll have to think about that on the way on the drive home from Toledo back to Akron.
So a couple of different methods there.
I'm not sure like either one probably would take that second macro and develop it into a nice little add in, along with error handling, uh, and so on.
But, uh, let’s moving on today, I would like tutorial for asking that question, I want to thank you for stopping by. I'll see you next time for another net cast from MrExcel.
wrap every formula in a new formula. Hey, welcome back to the MrExcel net cast. I'm Bill Jelen.
Yesterday.
I was over in Perrysburg Ohio doing a seminar and Tory, right at the end of the seminar, asked a question that was very interesting tutorials as a whole bunch of different formulas, different formulas, right?
And she wants to select the range and wrap a new function around all of these formulas.
Like for example, maybe we want to add equal round after the equal sign and then comma zero at the end. Well, that's a tough, tough thing to do.
You know, I initially said, well, maybe, maybe what we could do is we could do, um, uh, find, replace and change every occurrence of the equal sign to a, for example, carrot equal sign that will make it not be formula anymore.
Uh, so now that they're not forming those, then we can kind of do things in a yeah. Couple of steps.
So we can take the equal sign and change it to equal round, open parentheses, replace all, uh, and then hope that there's only one parentheses in the formula. And boy, that's just a dangerous thing right there.
Uh, and that replaced all.
Uh, and then, find then once all that's done, we can change the carrot equals back to N equals and replace all click, okay, close, we'll get out of show form of this mode.
And now everything, uh, all of the formulas here have around, around them.
Um, but that wouldn't work if there was any other right parentheses in the range.
Like even these formulas up here that ran formulas is if we change the rate parentheses to write comma zero, that formula just goes bad.
So maybe the way to go, I'm going to switch over to VBA alt F 11, uh, selected, uh, here, insert module, and then a tiny little macro, uh, add route for each cell in selection.
Old formulas equals two cell dot formula.
New formula is equal to a, we start out with the literal equal round open parentheses, and then the mid of the old format starting with two now, mid and VBA is a lot like mid in Excel.
Uh, but we just have to say, we don't have to specify a length. We can just say it starts at two.
If you don't specify how long it goes all the way to the end, why don't we start a two that's because I want to get rid of the opening equal sign?
And then at the end, rapid in comma zero compresses, zero cell formula is equal to new formula. Uh, so let's come back here.
I'm going to select the whole range, uh, all F8 all F8 and then run the macro. And, uh, it adds the round function around the whole thing.
This could in theory, be work, uh, you know, could do other things, for example, all of 11. So we want to have the old formula and then maybe wrap everything in the proper function.
All of that doesn't make a lot of sense today. Uh, we need to proper everything.
And so just click run and see.
Now we've added the, uh, proper to all of these, of course, that are other formulas go bad, just because I changed the numbers to text.
But you could use this method with a little bit of VBA to add just about any kind of our wrapper function around all of the formulas and arrange. It's certainly an interesting question.
If you're watching this on YouTube and you have a better idea of how to do this, please feel free to leave us a comment.
Uh, you know, uh, as I said, it was Tory's question right at the end of the seminar, I'm like, Oh, I'll have to think about that on the way on the drive home from Toledo back to Akron.
So a couple of different methods there.
I'm not sure like either one probably would take that second macro and develop it into a nice little add in, along with error handling, uh, and so on.
But, uh, let’s moving on today, I would like tutorial for asking that question, I want to thank you for stopping by. I'll see you next time for another net cast from MrExcel.
