How to Split a Word in Excel
Hey I’ve got a couple of cool formulas for today. Welcome back to the Mr. Excel netcast. I’m Bill Jelen. Today questions send in the Facebook from Ram has a big large word up here in cell A1. He needs to break that out letter by letter by letter and count how many times the letters occur. So very easy, when we say equal mid, this big large word up here press F4 and then we want to start at the right position. So we want to say row of 1:1. That’s just a geeky way running a number one. The nice thing is it will change the two as we copy them for a length of one.
So there’s our first letter and as we copy that down it gives us all letters in a formula. Check it out the row of one to one, change it to two. Two, two that gives us a second character and the third character and the fourth character and the fifth character, if you would need that to go across then you would use column of A:A alright and then Ram says I need to count. How many times each thing occurs, well that’s just a simple little count F look through all these letters over here and see if it’s equal to this particular letter and we get our accounts.
So I send that off to Ram and I said “You know I would bet that you probably only want to see the letter A up here once” and he says, “Yeah, it has to be unique. This was so easy, so easy, so easy|” and now it’s all shoot. I shouldn’t even ask, right I should have sent a back and hope that he was happy.
One solution is just to come here, select that whole area, insert pivot table, okay, put the letters down the left hand side, put the letters in the sum of values and there’s every letter how many times that occurs. Of course that’s not a formula solution. So you know the only thing I can clamp with here, it bounces one of my gig to see if it may be a good dealing podcast and Mike says, “Oh, this is an ugly one.”
The only thing I could come up with this big huge array formula. The array formula tries to do it fine. It says let’s go look for the first letter in the text and when we get that see if the fine is equal to the particular row. So in other words look for that first A and see if it’s in row one. If it is then I want the A otherwise I want quote, quotes so what’s that’s going to do is when we find the first A it will say well this is at one, “Oh, good I’m in row one.” Let’s give me but when I’m down here later on we get, we are over the next day is I don’t know, let’s see it’s down here in about the 11th character. It will say, “Hey well I’m currently looking at the 11th character, where’s the fine” that the fine is in, the first position and so that’s not the match and we’re going to get blanks.
All right so I press Control Shift Enter, copy that down and what you get then is you get unique letters but then you just get blank spaces where nothing is found and now unfortunately we can’t do a countif because we’re not have all the letters so we have some product down here, some product come in usually get you some product. What I did was I said go through look for the mid of A1 give me all the values from one through 29 and see if that’s equal to the current value A and got to put a minus, minus before that so we’re really going to just taking the arrays and array of truths and falsies converting it into ones and zeros and then summing them up to come up with that interest.
So really overall a very, very ugly question from Ram, you wouldn’t expect this something that tough would come in from Facebook, right. Facebook is supposed to be fun and not in this case. So hey if you have a better solution and maybe a macro or UDF or something I feel for the sheet. Bill@MrExcel.com. Make sure you stop by tomorrow for a doing podcast where we like it and I take a look at a very interesting problem. Thanks for stopping by and I’ll see you next time for another netcast from Mr. Excel.
Transcription by:
Scribe4you Transcription Services