The Question: "Can you extract a person's initials from their name?" Today, in Episode #1344, Mike 'ExcelIsFun' Girvin and Bill 'MrExcel' Jelen offer two (2) methods [one using a formula and another using a VBA function] to solve this problem and answer the question.
Transcript of the video:
Hey! Welcome back it's another dueling Excel podcast.
I'm Bill Jelen from MrExcel.
We'll be joined by Mike Girvin from Excel Is Fun.
Someone sent this question in via YouTube to Mike.
They have names over here, is kind of last name followed by first name.
Although, sometimes there's initial and they want to get the first and last letters kind of like a monogram.
This is a tough one.
I have no idea how Mike is gonna solve this without VBA.
But I know, I'm going straight over to the VBA.
So, we'll do [ alt F11 ] and then insert module.
Once I have that module, there's module 1.
Paste some code that I wrote.
Let's trying to get this away, so we can see it.
So, I created a new it's not a sub it's actually a function and I call that function Monogram and I'm gonna pass it the text in the cell and first thing I did was I used the trim function to get rid of leading and trailing spaces and also any interior double spaces started out with a monogram being equal to nothing and I know, that the last name is in that first character.
So, I'm gonna use that last, from there I start to go, for I equals 1 to the length of that text, and if it's equal to a space, then what I'm going to do is, I'm going to take this monogram text and add the next character.
So, we'll shoot through there, that'll get in theory the initial of the first name and any middle initial and then at the very end we take the monogram and add the leftmost character, which if everything goes well should be the last name.
Let's switch back to Excel and try it here, equal MONOGRAM.
Hoping get here is DJ, there we go.
Copy that down, and even there we have a middle initial, I'm getting the middle initials, okay.
So, like I realized this wasn't exactly how they asked for it, but it's how I think it should be done.
So, there Mike! Let's see what you have.
Mike: Thanks, MrExcel.
Oh man! Monogram VBA, totally beautiful and guess what it's getting that last letter, which is the middle of that so it goes D, A, S.
But I don't know VBA, so I'm stuck with formulas and functions.
All right! Hey! I'm gonna start off with the trim function just like MrExcel's VBA.
He asked the VBA to come and use the worksheet function trim.
I'm going to use the worksheet function trim, all it does is it get rid gets rid of all spaces except for single spaces between words.
[ Control enter ], and I'm going to copy this down.
Okay, so that just gives us the words with spaces, I need to get at that letter first.
So, I'm going to in the cell hit [ F2 ] and I'm actually going use this trim a number of times.
So, I'm going to highlight it and copy.
Now, I'm going to use the replace because I'm trying to get that, I'm going to say, hey! Replace go from the first character to wherever the spaces and replace it with blank and all that will be left is Dina.
So, I'm going to type replace, there's the old text that trim, comma the starting number.
Well, I want 1 always.
So, I'm gonna type a 1, comma, the number of characters, we'll have no idea because if the space is in a different position each time, no problem.
I'll use the search function.
The SEARCH function can search for a certain text and I'm going to do space, double quote, space, double quote, comma and within what, I'm going to [ control V ], to place my trim.
Now, right now, if I highlight this and hit [ F9 ], I can see that the search will tell me hey!
It's the six character, so replace is going to go from 1 to 6.
[ Control Z ], and I need to comma and that new text, it's double-quote, double-quote.
Now, I want to make sure that this one has a space because it's a space, this has nothing that means hey!
Nothing blank, so [ control enter ] and there it is replace went from the first character to the space and replaced it with blank.
Now, I need that first letter right there.
So, I'm going to [ F2 ] and simply use LEFT around this, hey! LEFT function, look at that thing there and give me just the first character.
[ Control enter ], double click and send it down.
I'm going to check, you know it looks like I have an extra little space there.
So, I'm going to do the LEN function just make sure, I'm on right track, and I should have one all the way down.
Okay, that looks like it's working.
I'm going to delete that.
Now, all I have to do is I have the D.
Now, I just need the J, so all I have to do is join, and I'm going to use the ampersand [ shift 7 ] and then double quotes period and double quotes.
I'm gonna join that with a period and I'm gonna join this with the left function again, but the left unlike up here.
I'm just going to [ control V ] because that'll give me left 1 and that will give me the J.
So, comma number of characters 1, close parenthesis and then quickly ampersand, double quote, period and in double quote, [ control enter ] and now double click and send it down.
There we have it.
So, I have an S R but I'm not getting the L not as cool as that monogram for VBA from MrExcel, but there it is.
All right! Throw it over to MrExcel.
MrExcel: Mike! That was beautiful, an amazing formula.
I think to a certain extent, knowing VBA actually makes you a lot weaker in Excel because rather than having to try and figure out a formula like that you can just flip over to VBA and do everything.
Plug, Slaying Excel Dragons, Mike this is great news.
It's Friday February 18th, if you remember that was the Bind By Date, right there February 18th and check this out text status, cover status, it is shipping.
That's right, books are leaving Harbor, Michigan, today.
Heading to the distributor in Chicago.
They'll be available at Amazon probably, middle to late next week.
So, everyone check that out all kinds of great, great Excel from the foundation all the way up.
Want to thank everyone for stopping by, see you next week for another dueling Excel podcast from MrExcel and Excel Is Fun.
I'm Bill Jelen from MrExcel.
We'll be joined by Mike Girvin from Excel Is Fun.
Someone sent this question in via YouTube to Mike.
They have names over here, is kind of last name followed by first name.
Although, sometimes there's initial and they want to get the first and last letters kind of like a monogram.
This is a tough one.
I have no idea how Mike is gonna solve this without VBA.
But I know, I'm going straight over to the VBA.
So, we'll do [ alt F11 ] and then insert module.
Once I have that module, there's module 1.
Paste some code that I wrote.
Let's trying to get this away, so we can see it.
So, I created a new it's not a sub it's actually a function and I call that function Monogram and I'm gonna pass it the text in the cell and first thing I did was I used the trim function to get rid of leading and trailing spaces and also any interior double spaces started out with a monogram being equal to nothing and I know, that the last name is in that first character.
So, I'm gonna use that last, from there I start to go, for I equals 1 to the length of that text, and if it's equal to a space, then what I'm going to do is, I'm going to take this monogram text and add the next character.
So, we'll shoot through there, that'll get in theory the initial of the first name and any middle initial and then at the very end we take the monogram and add the leftmost character, which if everything goes well should be the last name.
Let's switch back to Excel and try it here, equal MONOGRAM.
Hoping get here is DJ, there we go.
Copy that down, and even there we have a middle initial, I'm getting the middle initials, okay.
So, like I realized this wasn't exactly how they asked for it, but it's how I think it should be done.
So, there Mike! Let's see what you have.
Mike: Thanks, MrExcel.
Oh man! Monogram VBA, totally beautiful and guess what it's getting that last letter, which is the middle of that so it goes D, A, S.
But I don't know VBA, so I'm stuck with formulas and functions.
All right! Hey! I'm gonna start off with the trim function just like MrExcel's VBA.
He asked the VBA to come and use the worksheet function trim.
I'm going to use the worksheet function trim, all it does is it get rid gets rid of all spaces except for single spaces between words.
[ Control enter ], and I'm going to copy this down.
Okay, so that just gives us the words with spaces, I need to get at that letter first.
So, I'm going to in the cell hit [ F2 ] and I'm actually going use this trim a number of times.
So, I'm going to highlight it and copy.
Now, I'm going to use the replace because I'm trying to get that, I'm going to say, hey! Replace go from the first character to wherever the spaces and replace it with blank and all that will be left is Dina.
So, I'm going to type replace, there's the old text that trim, comma the starting number.
Well, I want 1 always.
So, I'm gonna type a 1, comma, the number of characters, we'll have no idea because if the space is in a different position each time, no problem.
I'll use the search function.
The SEARCH function can search for a certain text and I'm going to do space, double quote, space, double quote, comma and within what, I'm going to [ control V ], to place my trim.
Now, right now, if I highlight this and hit [ F9 ], I can see that the search will tell me hey!
It's the six character, so replace is going to go from 1 to 6.
[ Control Z ], and I need to comma and that new text, it's double-quote, double-quote.
Now, I want to make sure that this one has a space because it's a space, this has nothing that means hey!
Nothing blank, so [ control enter ] and there it is replace went from the first character to the space and replaced it with blank.
Now, I need that first letter right there.
So, I'm going to [ F2 ] and simply use LEFT around this, hey! LEFT function, look at that thing there and give me just the first character.
[ Control enter ], double click and send it down.
I'm going to check, you know it looks like I have an extra little space there.
So, I'm going to do the LEN function just make sure, I'm on right track, and I should have one all the way down.
Okay, that looks like it's working.
I'm going to delete that.
Now, all I have to do is I have the D.
Now, I just need the J, so all I have to do is join, and I'm going to use the ampersand [ shift 7 ] and then double quotes period and double quotes.
I'm gonna join that with a period and I'm gonna join this with the left function again, but the left unlike up here.
I'm just going to [ control V ] because that'll give me left 1 and that will give me the J.
So, comma number of characters 1, close parenthesis and then quickly ampersand, double quote, period and in double quote, [ control enter ] and now double click and send it down.
There we have it.
So, I have an S R but I'm not getting the L not as cool as that monogram for VBA from MrExcel, but there it is.
All right! Throw it over to MrExcel.
MrExcel: Mike! That was beautiful, an amazing formula.
I think to a certain extent, knowing VBA actually makes you a lot weaker in Excel because rather than having to try and figure out a formula like that you can just flip over to VBA and do everything.
Plug, Slaying Excel Dragons, Mike this is great news.
It's Friday February 18th, if you remember that was the Bind By Date, right there February 18th and check this out text status, cover status, it is shipping.
That's right, books are leaving Harbor, Michigan, today.
Heading to the distributor in Chicago.
They'll be available at Amazon probably, middle to late next week.
So, everyone check that out all kinds of great, great Excel from the foundation all the way up.
Want to thank everyone for stopping by, see you next week for another dueling Excel podcast from MrExcel and Excel Is Fun.
