The PROPER function never manages to capitalize the interior C in McCartney. In today's podcast, an interesting way to solve this problem with a formula. Episode 722 shows you how.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
If you've been in one of my Excel seminars.
I usually show this concatenation trick, where we take the text from A2 and the text from B2 and we use the PROPER function.
So the proper of A2, ampersand, quotes space, quote, (A2&'[ space ]'&B2) ampersand, B2 and basically it takes that text puts a space between it and makes it an upper and lower case.
But I always show is the second record is Paul McCartney and it doesn't get the interior 'C' in McCartney, correct.
I've done this 100 times in the seminars.
Last week, I was at Jefferson Wells in Chicago and a fellow named Adrian said, "Hey! I have a great solution for that." I run into that one.
He said here's what you do.
Basically, we're going to take a look at the first two letters of the last name, equal if the left of B2 comma 2 is equal to Mc, then we're going to jump through some hoops or intake the proper of A2, ampersand and then in quotes a space and the M lowercase 'c' and then the proper of the mid of B2.
Starting at Position 3 and we'll just put a big number that will include enough characters to get any possible text.
So, 20.
Close the parenthesis on the proper, and then if that's not true, we just go back to the original formula.
The proper of A2 ampersand, closed space, quote, ampersand, B2.
Close the IF function and will copy it down here and see if sure enough, we do get the interior 'c' in McCartney, capitalized.
So, great idea.
I want to thank Adrian for suggesting that good way in case you have a large Irish contingent.
And you want to make sure those interior 'c's' are capitalized.
This is the best solution I've ever encountered rather than just going through changing the formulas of values and fixing them manually.
Well, there you have it.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
If you've been in one of my Excel seminars.
I usually show this concatenation trick, where we take the text from A2 and the text from B2 and we use the PROPER function.
So the proper of A2, ampersand, quotes space, quote, (A2&'[ space ]'&B2) ampersand, B2 and basically it takes that text puts a space between it and makes it an upper and lower case.
But I always show is the second record is Paul McCartney and it doesn't get the interior 'C' in McCartney, correct.
I've done this 100 times in the seminars.
Last week, I was at Jefferson Wells in Chicago and a fellow named Adrian said, "Hey! I have a great solution for that." I run into that one.
He said here's what you do.
Basically, we're going to take a look at the first two letters of the last name, equal if the left of B2 comma 2 is equal to Mc, then we're going to jump through some hoops or intake the proper of A2, ampersand and then in quotes a space and the M lowercase 'c' and then the proper of the mid of B2.
Starting at Position 3 and we'll just put a big number that will include enough characters to get any possible text.
So, 20.
Close the parenthesis on the proper, and then if that's not true, we just go back to the original formula.
The proper of A2 ampersand, closed space, quote, ampersand, B2.
Close the IF function and will copy it down here and see if sure enough, we do get the interior 'c' in McCartney, capitalized.
So, great idea.
I want to thank Adrian for suggesting that good way in case you have a large Irish contingent.
And you want to make sure those interior 'c's' are capitalized.
This is the best solution I've ever encountered rather than just going through changing the formulas of values and fixing them manually.
Well, there you have it.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel.
