Shift-Del sends in some alternative formulas to solve the problem from episode 1043. To see uses of SUBSTITUTE and REPLACE in Excel, check out Episode 1097.
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.
Basically you start out with massive amounts of data.
So how we gonna analyze as well let's fire up a pivot table.
Lets see if you can solve this problem.
Hey all right, welcome back. It's another MrExcel netcast.
I'm Bill Jelen.
We're going to go back to episode 1043 . Back in episode 1043 I had this data with name in column A, name and address in column B, And I wrote this formula over here in column C.
To strip out the name from column B to get the address only, I used the MID function and the LEN function.
Someone at the MrExcel message board sign up there and they use the handle if shift+delete. So I don't know who they are where they're from, but they sent 4 cool formula variations to this and I like them.
So I'm going to cover them right here.
the SUBSTITUTE function.
Substitute says. Hey, we have some text here in B2 and within that text, I want you to find the name from A2 and substitute the name with nothing quote quote.
Check this out now the problem that we have is that that gives us a leading space because there's a space between company and 1292 and so the two suggestions one was to use the TRIM.
Function TRIM ofcourse removes leading and trailing spaces.
Solves our problem or instead of using TRIM we can get rid of TRIM and say. Hey, in the SUBSTITUTE function instead of looking for A3 we want to look for A3&" " and that will take care of it as well, so the substitute function very cool.
Another one is the REPLACE function.
REPLACE function says take the text from B, start at position one and go to the length of A1 for 1 and substitute that whole thing with "" and that gets rid of it as well and then finally using the right function, this is probably the most convoluted, how we will use the right of B5 and to figure out which right most characters we use, the length of all B5 minus the length of A5-1 So several different ways to go there.
You know I always say there's a couple of ways to solve every problem in excel.
Here's an example where 5 very cool functions and hey, shift+del.
Thanks for those great suggestions.
You send me your postal address, I'll send you one of my excel master pins.
Thanks for sending that in and thanks to you for stopping by.
Will see you next time for another netcast from MrExcel.
Basically you start out with massive amounts of data.
So how we gonna analyze as well let's fire up a pivot table.
Lets see if you can solve this problem.
Hey all right, welcome back. It's another MrExcel netcast.
I'm Bill Jelen.
We're going to go back to episode 1043 . Back in episode 1043 I had this data with name in column A, name and address in column B, And I wrote this formula over here in column C.
To strip out the name from column B to get the address only, I used the MID function and the LEN function.
Someone at the MrExcel message board sign up there and they use the handle if shift+delete. So I don't know who they are where they're from, but they sent 4 cool formula variations to this and I like them.
So I'm going to cover them right here.
the SUBSTITUTE function.
Substitute says. Hey, we have some text here in B2 and within that text, I want you to find the name from A2 and substitute the name with nothing quote quote.
Check this out now the problem that we have is that that gives us a leading space because there's a space between company and 1292 and so the two suggestions one was to use the TRIM.
Function TRIM ofcourse removes leading and trailing spaces.
Solves our problem or instead of using TRIM we can get rid of TRIM and say. Hey, in the SUBSTITUTE function instead of looking for A3 we want to look for A3&" " and that will take care of it as well, so the substitute function very cool.
Another one is the REPLACE function.
REPLACE function says take the text from B, start at position one and go to the length of A1 for 1 and substitute that whole thing with "" and that gets rid of it as well and then finally using the right function, this is probably the most convoluted, how we will use the right of B5 and to figure out which right most characters we use, the length of all B5 minus the length of A5-1 So several different ways to go there.
You know I always say there's a couple of ways to solve every problem in excel.
Here's an example where 5 very cool functions and hey, shift+del.
Thanks for those great suggestions.
You send me your postal address, I'll send you one of my excel master pins.
Thanks for sending that in and thanks to you for stopping by.
Will see you next time for another netcast from MrExcel.
