Another way to solve yesterday's problem is to use the =SUBSTITUTE function instead of LEFT and LEN. Episode 792 will show you how.
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:
Hey. Welcome back to the MrExcel netcast. I’m Bill Jelen. Well, it’s the 1st of July.
We’re up to episode number 792.
In the last episode, I talked about how to get rid of the word TOTAL over here in column D, and I use this formula using LEFT and LEN, and there's actually a much better way to do this.
You know, there's 3 or 4 of 5 ways to do everything in Excel, and there's a function that I rarely use called SUBSTITUTE, the SUBSTITUTE function.
It says, hey, look over there in D6, and we want to take this old text.
So, in “, I'm going to put a space in the word TOTAL, and then the new text is just going to be “”. I don't want anything there at all, and if I wanted to get rid of the second or third occurrence of the word TOTAL, I could use the INSTANCE NUM, but I don't have to do that.
It will take the word TOTAL out.
[ =SUBSTITUTE(D6,“ TOTAL”,“”) ] So, again, just like yesterday, in order to enter this function everywhere, we're going to select all of the cells next to the totals, use ALT+; to select the visible cells only, type that formula, and then press CONTROL+ENTER to enter that similar formula the whole way down, and then, of course, you know, really, the fastest way of all is just to select all this data and use EDIT, REPLACE, and change every occurence of just space TOTAL to nothing, use REPLACE ALL, and you achieve the same effect.
So, several different ways to solve that problem.
Now, of course, we can do VLOOKUPs into this data in order to get the totals for another worksheet.
Well, there you have it.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
We’re up to episode number 792.
In the last episode, I talked about how to get rid of the word TOTAL over here in column D, and I use this formula using LEFT and LEN, and there's actually a much better way to do this.
You know, there's 3 or 4 of 5 ways to do everything in Excel, and there's a function that I rarely use called SUBSTITUTE, the SUBSTITUTE function.
It says, hey, look over there in D6, and we want to take this old text.
So, in “, I'm going to put a space in the word TOTAL, and then the new text is just going to be “”. I don't want anything there at all, and if I wanted to get rid of the second or third occurrence of the word TOTAL, I could use the INSTANCE NUM, but I don't have to do that.
It will take the word TOTAL out.
[ =SUBSTITUTE(D6,“ TOTAL”,“”) ] So, again, just like yesterday, in order to enter this function everywhere, we're going to select all of the cells next to the totals, use ALT+; to select the visible cells only, type that formula, and then press CONTROL+ENTER to enter that similar formula the whole way down, and then, of course, you know, really, the fastest way of all is just to select all this data and use EDIT, REPLACE, and change every occurence of just space TOTAL to nothing, use REPLACE ALL, and you achieve the same effect.
So, several different ways to solve that problem.
Now, of course, we can do VLOOKUPs into this data in order to get the totals for another worksheet.
Well, there you have it.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
