Hey! Welcome back to the Mr. Excel netcast I am Bill Jelen. Basic: we start out with massive amounts of data and how are we going to analyze this? Well, let's fire up the activity table and see if we can solve this problem.
Alright, welcome back to Mr. Excel netcast, I’m Bill Jelen. Today’s question, sent in by Dan via Facebook. Dan has a pretty strange looking file here. At column A, she has a 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 Dan 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 equal mid of 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. So, I’m going to ask for the LEN and that’s the length function of A2. And if you think about it, Sure Doorbell Company, that’s going be some number of characters. And then there’s the space and then I want to start 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, you just ask for some incredibly huge number that will get all the characters and it does in fact not pad that result, you know, it’s not a whole bunch of extra spaces in the end because the 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.
So, 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 from Mr. Excel.
Transcription by:
Scribe4you Transcription Services