The ugly data today has SmithJohn without a space. How can you split the text at the capital letter? Mike and Bill duel with a VBA and a formula method.
Transcript of the video:
Bill: Hey welcome back, it's time for another Dueling Excel podcast.
I'm Bill Jelen from MrExcel, I'll be joined by Mike Girvin from Excel Is Fun, this is our episode 171 - Break CamelCase Into Words!
Well this is a fascinating question, as soon as I saw this one, Mike and I said, this would be a great duel.
Robert from YouTube said "Is there a way to split first and last names and a data dump that has no spaces, for example: Bill Jelen?" With a capital B, capital J, no space.
Alright, so basically what we're going to do, is data text to columns, and have it split at the second capital letter, isn't that crazy?
I don't think the Power Query has anything like that, although I might be burned, it's possible.
So here's what we know, capital letter A is code 65, capital letter Z is 90, and then 97 to 122, so what I'm looking for, is I'm looking for where the code, various elements, various characters, in that string is between 65 and 90.
And while there might be a formula that would do the MID of, you know, from the row of the one to the length of the whole thing.
I am just switching right over to VBA, and wrote a nice little function here called =FindCap . I'm going to pass in some text and tell it which capital I'm looking for.
If I don't specify which capital I'm looking, for then I'm going to assume I'm looking for the first one, in this case I'm really looking for the second one.
Figure out how many I've found for i=1 to the length of my text, look at this character, the Asc of this character, ASC, that's the ASCII code.
It's funny in Excel it's equal code, but here in VBA it's ASC.
And then check and see what character we get, if it's between 65 and 90, then we'll increment the number of caps found.
So the first time B in Bill is going to give us one caps found, J in Jelen is going to give us a second caps found.
If what we found is what we're looking for, then I just return whatever position we're on in the loop, and exit the function.
Alright, so check this out: =FindCap in that text, ,2 , it is in the 7th place, it says 1 2 3 4 5 6, 7.
There we go, double click to copy that down, and then it becomes a simple matter of =LEFT of here, comma, the Findcap -1, which should give us the first name.
And then the =MID of the original text, comma, starting in that position, comma, I'm just going to ask for a huge number, and I know that will get out to the end.
Alright so there you go.
Mike, I am just falling back to VBA, let me know if you have a way to deal with this!
Mike: Thanks MrExcel!
Fall back to VBA!
At least you have VBA to fall back to.
I love the FindCap, totally awesome, but I'm stuck with doing formulas.
Alright, let's come over to this sheet right here.
Now, when I look at this, I see two caps, and you know what, I don't want to mess around with two caps, I only want one cap in my text string to have to look through.
So I'm first going to remove that first cap letter.
So I'm going to use the RIGHT, I'm saying "Hey go get it from the right", comma, how many characters, well if I gave it the length of all of that, it would get all of the characters, I'm just going to subtract one, BOOM!
And that got rid of that first cap letter.
Now the next thing I want to do is actually create a list of all the capital letters that I might encounter, A to Z cap.
MrExcel used code to go from letter to number, I'm going to use the complement to that, or the opposite of that, the character!
This will go from a number, 65, to the letter.
Now to create this list, I'm going to instead say 64, that's one before, and then add ROW.
Now ROW function just looks at the particular row, 1 2 3, so that'll work, it will increment and add 1 as we go down.
I'm going to.
This formula here is temporary, we'll delete it after we use the letters.
Double click and send it down.
And there we have our letters.
Now, I was thinking about this, and I was like "What functions can actually look at capital letters?" Well there certainly is UPPER, right?
Well that just changes everything to an UPPER, I don't want that.
There's EXACT.
EXACT actually compares to text strings, that's case-sensitive.
But wait a second, the one we can use is FIND!
FIND an SEARCH will do exactly what we want, search through here and find the position of a particular character, but check out FIND - case-sensitive!
So that's exactly what we want.
Now FIND text, you usually put one thing in here, but I'm going to give it a bunch of things.
CTEL+Shift+Down arrow, CTRL+Backspace, to jump back to the active cell.
Now, I don't want those cell references, so I'm going to click on the screen tip and hit F9 to evaluate.
Now if I left it like this, it would violate Excel's golden rule, right, you're not supposed to hardcode things into formulas, EXCEPT if the things you're hardcoding will NEVER change.
And guess what, I can't think of a better example than letters in the alphabet, those are never going to change.
So I'm just going to leave it like that, I'll delete this E column in just a second.
Now that's a function argument, array operation FIND, it'll actually find something for every one of those items, comma, and I'm going to say "Within this cell right here", ) . Now again, that's a function argument array operation, it tells FIND to find, to spit out a bunch of answers.
So if I highlight it, F9, there's all my answers.
There's the 6 I'm after!
Now, when we get value errors, or errors like this, that'll mess up most functions.
If I put MIN or MAX or SUM or some aggregate function to try and get out that six there, the value arrow will just mess it up.
Now I could use AGGREGATE which avoids error, CTRL+Z, but I'm actually going to go way back and use LOOKUP!
Now LOOKUP it amazing, it'll do a horizontal lookup, vertical lookup, it will not see those errors, meaning it will just skip over them, and it can handle array operations like this without CTRL+Shift+Enter.
The only trick is, if I comma this lookup vector, that's what I'm looking up, what in the world do I need is a lookup value?
Well I just need to put some big number, so the some big number I'm going to put is 2^15.
Why 2^15?
Because Excel allows, as a max number of characters, 32.767, and 2^15 is one more than that.
So that will work perfect, you've got to be kidding me, watch this.
Enter, and we have a 6!
Now we need to +1, because we actually want the 7th position, and that form will work all the way down.
Now I want to mash it all together, so see, B2 is pointing right there.
So I'm going to copy this in edit mode, CTRL+C, Esc.
Come over here, double click, CTRL+V, CTRL+Enter, that will work.
Now I'll delete this, and I'm going to point to the edge and hold Shift and drag it over!
And so that will work all the way down.
Now to get the first, I'm simply going to use, just like MrExcel did, LEFT, give me the left of this comma, and I want 7, but that'll give me one too many, so I -1) . So that got me Thomas.
Now for last I'm going to use the REPLACE function, REPLACE, there's the old text, comma, the start position of characters to replace, I'm starting at 1, comma, how many characters do I want.
Well I need to add 6, so I'm going to say 7 - 1.
That'll give me the first 7 characters, comma, and then replaces what do you want to replace, or what do you want to put in its place, "", that's the syntax for show nothing, CTRL+Enter.
Now I can highlight these, double click and send it down.
Wow, that is wild, a bunch of cool stuff.
Throw it back to MrExcel!
Bill: Hey wow, Mike, thought you were going to go with MID and INDIRECT and ROW and do some magic there, but instead completely different direction with LOOKUP, FIND, RIGHT, LEN, not to mention CHAR and ROW to generate the letters, and that great trick with 2^15 -1.
Love these duels, we come up with so many different ways to do this one great formula there.
Boy, it'd be nice if there was really just a faster, easier way, but here we are.
Alright, well, thank you everyone for stopping by, we'll see you next week for another Dueling Excel podcast from MrExcel and excelisfun!
I'm Bill Jelen from MrExcel, I'll be joined by Mike Girvin from Excel Is Fun, this is our episode 171 - Break CamelCase Into Words!
Well this is a fascinating question, as soon as I saw this one, Mike and I said, this would be a great duel.
Robert from YouTube said "Is there a way to split first and last names and a data dump that has no spaces, for example: Bill Jelen?" With a capital B, capital J, no space.
Alright, so basically what we're going to do, is data text to columns, and have it split at the second capital letter, isn't that crazy?
I don't think the Power Query has anything like that, although I might be burned, it's possible.
So here's what we know, capital letter A is code 65, capital letter Z is 90, and then 97 to 122, so what I'm looking for, is I'm looking for where the code, various elements, various characters, in that string is between 65 and 90.
And while there might be a formula that would do the MID of, you know, from the row of the one to the length of the whole thing.
I am just switching right over to VBA, and wrote a nice little function here called =FindCap . I'm going to pass in some text and tell it which capital I'm looking for.
If I don't specify which capital I'm looking, for then I'm going to assume I'm looking for the first one, in this case I'm really looking for the second one.
Figure out how many I've found for i=1 to the length of my text, look at this character, the Asc of this character, ASC, that's the ASCII code.
It's funny in Excel it's equal code, but here in VBA it's ASC.
And then check and see what character we get, if it's between 65 and 90, then we'll increment the number of caps found.
So the first time B in Bill is going to give us one caps found, J in Jelen is going to give us a second caps found.
If what we found is what we're looking for, then I just return whatever position we're on in the loop, and exit the function.
Alright, so check this out: =FindCap in that text, ,2 , it is in the 7th place, it says 1 2 3 4 5 6, 7.
There we go, double click to copy that down, and then it becomes a simple matter of =LEFT of here, comma, the Findcap -1, which should give us the first name.
And then the =MID of the original text, comma, starting in that position, comma, I'm just going to ask for a huge number, and I know that will get out to the end.
Alright so there you go.
Mike, I am just falling back to VBA, let me know if you have a way to deal with this!
Mike: Thanks MrExcel!
Fall back to VBA!
At least you have VBA to fall back to.
I love the FindCap, totally awesome, but I'm stuck with doing formulas.
Alright, let's come over to this sheet right here.
Now, when I look at this, I see two caps, and you know what, I don't want to mess around with two caps, I only want one cap in my text string to have to look through.
So I'm first going to remove that first cap letter.
So I'm going to use the RIGHT, I'm saying "Hey go get it from the right", comma, how many characters, well if I gave it the length of all of that, it would get all of the characters, I'm just going to subtract one, BOOM!
And that got rid of that first cap letter.
Now the next thing I want to do is actually create a list of all the capital letters that I might encounter, A to Z cap.
MrExcel used code to go from letter to number, I'm going to use the complement to that, or the opposite of that, the character!
This will go from a number, 65, to the letter.
Now to create this list, I'm going to instead say 64, that's one before, and then add ROW.
Now ROW function just looks at the particular row, 1 2 3, so that'll work, it will increment and add 1 as we go down.
I'm going to.
This formula here is temporary, we'll delete it after we use the letters.
Double click and send it down.
And there we have our letters.
Now, I was thinking about this, and I was like "What functions can actually look at capital letters?" Well there certainly is UPPER, right?
Well that just changes everything to an UPPER, I don't want that.
There's EXACT.
EXACT actually compares to text strings, that's case-sensitive.
But wait a second, the one we can use is FIND!
FIND an SEARCH will do exactly what we want, search through here and find the position of a particular character, but check out FIND - case-sensitive!
So that's exactly what we want.
Now FIND text, you usually put one thing in here, but I'm going to give it a bunch of things.
CTEL+Shift+Down arrow, CTRL+Backspace, to jump back to the active cell.
Now, I don't want those cell references, so I'm going to click on the screen tip and hit F9 to evaluate.
Now if I left it like this, it would violate Excel's golden rule, right, you're not supposed to hardcode things into formulas, EXCEPT if the things you're hardcoding will NEVER change.
And guess what, I can't think of a better example than letters in the alphabet, those are never going to change.
So I'm just going to leave it like that, I'll delete this E column in just a second.
Now that's a function argument, array operation FIND, it'll actually find something for every one of those items, comma, and I'm going to say "Within this cell right here", ) . Now again, that's a function argument array operation, it tells FIND to find, to spit out a bunch of answers.
So if I highlight it, F9, there's all my answers.
There's the 6 I'm after!
Now, when we get value errors, or errors like this, that'll mess up most functions.
If I put MIN or MAX or SUM or some aggregate function to try and get out that six there, the value arrow will just mess it up.
Now I could use AGGREGATE which avoids error, CTRL+Z, but I'm actually going to go way back and use LOOKUP!
Now LOOKUP it amazing, it'll do a horizontal lookup, vertical lookup, it will not see those errors, meaning it will just skip over them, and it can handle array operations like this without CTRL+Shift+Enter.
The only trick is, if I comma this lookup vector, that's what I'm looking up, what in the world do I need is a lookup value?
Well I just need to put some big number, so the some big number I'm going to put is 2^15.
Why 2^15?
Because Excel allows, as a max number of characters, 32.767, and 2^15 is one more than that.
So that will work perfect, you've got to be kidding me, watch this.
Enter, and we have a 6!
Now we need to +1, because we actually want the 7th position, and that form will work all the way down.
Now I want to mash it all together, so see, B2 is pointing right there.
So I'm going to copy this in edit mode, CTRL+C, Esc.
Come over here, double click, CTRL+V, CTRL+Enter, that will work.
Now I'll delete this, and I'm going to point to the edge and hold Shift and drag it over!
And so that will work all the way down.
Now to get the first, I'm simply going to use, just like MrExcel did, LEFT, give me the left of this comma, and I want 7, but that'll give me one too many, so I -1) . So that got me Thomas.
Now for last I'm going to use the REPLACE function, REPLACE, there's the old text, comma, the start position of characters to replace, I'm starting at 1, comma, how many characters do I want.
Well I need to add 6, so I'm going to say 7 - 1.
That'll give me the first 7 characters, comma, and then replaces what do you want to replace, or what do you want to put in its place, "", that's the syntax for show nothing, CTRL+Enter.
Now I can highlight these, double click and send it down.
Wow, that is wild, a bunch of cool stuff.
Throw it back to MrExcel!
Bill: Hey wow, Mike, thought you were going to go with MID and INDIRECT and ROW and do some magic there, but instead completely different direction with LOOKUP, FIND, RIGHT, LEN, not to mention CHAR and ROW to generate the letters, and that great trick with 2^15 -1.
Love these duels, we come up with so many different ways to do this one great formula there.
Boy, it'd be nice if there was really just a faster, easier way, but here we are.
Alright, well, thank you everyone for stopping by, we'll see you next week for another Dueling Excel podcast from MrExcel and excelisfun!
