Make 1st Letter Upper Case

[erock24]

I have cells that have 1 or 2 words in them. Is there a formula I can use that will make the 1st letter of each word Upper Case. The whole range started out all caps. I used =lower(a1) to make them all lower. Now I just need 1st letter of each word upper.

 

[Jonmo1]

=Proper(a1)

 

[erock24]

cool.
Thank you very much.

 

[erock24]

Is there a way to get around this exception to the rule?

my cell = JOHN MCDOE
=proper(a1) = John Mcdoe
what I need = John McDoe

the =proper formula works perfectly except for these "Mc" exceptions.
Is there a way around this.

 

[dave3009]

Hi Erock

I know it is customary to post a solution but my brain has ceased operation at this point. I can get you to John Mc but am struggling with the bit that gives you Doe.

Hopefully you can utilise the first part of the formula to return the Doe bit

=IF(ISNUMBER(SEARCH("MC",A26)), PROPER(LEFT(A26,SEARCH("MC",A26)+1)), 0)

Also that 0 should be PROPER(A26)

Sorry I only got so far


HTH


Dave

 

[Scott Huish]

Perhaps:
=TRIM(SUBSTITUTE(PROPER(SUBSTITUTE(UPPER(" " & A1)," MC"," MC "))," ",""))

 

[facethegod]

Perhaps

=IF(NOT(ISERROR(SEARCH(" mc",A1))),REPLACE(PROPER(K5),SEARCH(" mc",A1)+3,1,UPPER(MID(K5,SEARCH(" mc",A1)+3,1))),PROPER(A1))

HTH

 

[erock24]

Hotpepper,
works almost perfectly, It loses the space between the 1st and the last name.

your formula returned JohnMcDoe
Is there a fix for this?

Thank you for your time and help.

 

[Scott Huish]

Sorry, that didn't show on the board correctly, I'll try it like this:

=TRIM(SUBSTITUTE(PROPER(SUBSTITUTE(UPPER(" " & A1)," MC"," MC "))," ",""))

There are supposed to be two spaces after the MC in the first dark blue MC and two spaces between the quotes in the second dark blue part.

 

[erock24]

Hotpepper,

works perfectly. Thank you very much.

facethegod. Yours also works perfectly.

Thank you all very much for your time and help.