Dawn's file has name in A and address in B. Except: the address field in column B contains the name again. Episode 1043 shows how to parse the variable-length name out from column B.
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, we start out with massive amounts of data.
How we're gonna analyze this. Well, let's fire up a pivot table.
Let's see, if you could solve this problem.
All right, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question send in by Don via Facebook.
Don has a pretty, strange looking file here in column A, she has company name and in column B, the address column.
She has company name and then the address.
Now, she didn't say where this came from but, I've seen quick books, do something like this before very annoying and Don says hey, I want to be able to parse the company name out of column B, remove it.
And get just everything else, she says there's no delimiter and you know, of course every company name is a different length.
So, you know anything I do like with text to columns is not working.
So, here's what i'm going to, do I'm going to add a new column C out here.
I'm going to call it address only and we're going to ask, for the MID function.
So, =MID(B2, and then it says well where do you want to start.
Well, this is interesting it's going to be a different character every time.
But, I can Use that information over in column A to figure out where to start.
Someone, asked for the LEN, that's the LENGTH function of A2 and if you think about it, Sure Doorbell Company that's gonna be some number of characters and then there's a space and then i want to start in the next character.
So, I'm going to say plus two and then finally the number of characters.
Well, you know i'm always torn here at this point, one side of me wants to take the length of B2 and subtract the length of A2 minus 2 and figure out the exact amount.
The other side of me says well, it just asks for some incredibly huge number that will get all the characters and it does.
In fact not, bad that result it you know, there's not a whole bunch of extra spaces because they're 150 characters it just cuts it off at the end.
So, that seems to be a good way to go, double click the fill handle to shoot it down, and we now have just the address.
There you have it.
Interesting way to use the MID function that always starts at a different location depending on the length of the name over in column A.
Hey, I wanna thank you for stopping by We'll see you next time for another netcast form MrExcel.
Well, thanks for stopping by.
We'll see you next time for another netcast form MrExcel.
I'm Bill Jelen.
Basically, we start out with massive amounts of data.
How we're gonna analyze this. Well, let's fire up a pivot table.
Let's see, if you could solve this problem.
All right, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question send in by Don via Facebook.
Don has a pretty, strange looking file here in column A, she has company name and in column B, the address column.
She has company name and then the address.
Now, she didn't say where this came from but, I've seen quick books, do something like this before very annoying and Don says hey, I want to be able to parse the company name out of column B, remove it.
And get just everything else, she says there's no delimiter and you know, of course every company name is a different length.
So, you know anything I do like with text to columns is not working.
So, here's what i'm going to, do I'm going to add a new column C out here.
I'm going to call it address only and we're going to ask, for the MID function.
So, =MID(B2, and then it says well where do you want to start.
Well, this is interesting it's going to be a different character every time.
But, I can Use that information over in column A to figure out where to start.
Someone, asked for the LEN, that's the LENGTH function of A2 and if you think about it, Sure Doorbell Company that's gonna be some number of characters and then there's a space and then i want to start in the next character.
So, I'm going to say plus two and then finally the number of characters.
Well, you know i'm always torn here at this point, one side of me wants to take the length of B2 and subtract the length of A2 minus 2 and figure out the exact amount.
The other side of me says well, it just asks for some incredibly huge number that will get all the characters and it does.
In fact not, bad that result it you know, there's not a whole bunch of extra spaces because they're 150 characters it just cuts it off at the end.
So, that seems to be a good way to go, double click the fill handle to shoot it down, and we now have just the address.
There you have it.
Interesting way to use the MID function that always starts at a different location depending on the length of the name over in column A.
Hey, I wanna thank you for stopping by We'll see you next time for another netcast form MrExcel.
Well, thanks for stopping by.
We'll see you next time for another netcast form MrExcel.
