Hey welcome back to Mr. Excel Net Cast. I’m Bill Jelen. Basically, we start out with massive amount of data, and say, “How are we going to analyze this?” Well, let’s fire up the pivot table and see if we could solve this problem.
Yeah, right welcome back, it’s another Mr. Excel Net Cast, I’m Bill Jelen. I’m going to go back to episode 1043, back in the episode 1043. I had this data with Name in Column A and Name and Address in Column B. I wrote this formula over here in Column C to strip out the name from Column B, to get the address only. I use the MID function and the LEM function. Some of the Mr. Excel message for the sign up there and the use/the handle of Shift Del, so I don’t know who they are or where they’re from but they send four cool formula variations to this and I like them so I'm going to cover it right here, the substitute function.
Substitutes says—hey we have some texts here in B2 and within that text, I want you to find the name from A tube 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 is a space in between the company and 1292 and the two suggestions were—one, was to use the trim, function trim of course removes leading and trailing spaces. It solves our problem. Or, instead of using trim, we can get rid of trim and say, in the substitute function, instead of looking for A3, were going to look for A3 & quote space quote and that will take care of it as well. So the substitute function is very cool.
Another one is the replace function, the replace function says, take the text from B. Start it at position one and go to the length of A1 for one and substitute that whole thing with quote-quote and that gets rid of it as well and then finally, using the right function, and this is probably the most convoluted, we’re going to use the right B5 and to figure out which rate most characters we use the length of all of B5 minus the length of A5 minus 1.
So, several different ways to go there, as I said, there’s a couple of ways to solve every problem in Excel. Here’s an example, a very cool function. Hey Shift Delete, thanks for those great suggestions you sent me, your postal address is also one of my Excel Master Pens, thanks for sending that in. And thanks to you for stopping by. We’ll see you next time for another Net Cast from Mr. Excel.
Well, thanks for stopping by, we’ll see you next time for another Net Cast from Mr. Excel.
Transcription by:
Scribe4you Transcription Services