Search code examples
exceltextexcel-formulacapitalize

Excel: Capitalize first character of each word

I the following text:

Your number's up (season 2)

I want to change it to:

Your Number's Up

However, I get this right now with the following code:

Your Number'S Up

=TRIM(PROPER(IFERROR(LEFT(A6976,FIND("(",A6976)-1),A6976)))

How can I fix this?

Solution

  • With data in A1, use:

    =SUBSTITUTE(PROPER(SUBSTITUTE(A1,"'","zzz")),"zzz","'")

    enter image description here

    EDIT#1:

    Do drop the parenthetical part, use:

    =MID(SUBSTITUTE(PROPER(SUBSTITUTE(A1,"'","zzz")),"zzz","'"),1,FIND("(",SUBSTITUTE(PROPER(SUBSTITUTE(A1,"'","zzz")),"zzz","'"))-1)

    EDIT#2:

    See if this is any better:

    =MID(SUBSTITUTE(PROPER(SUBSTITUTE(A1,"'","zzz")),"zzz","'"),1,FIND("(",SUBSTITUTE(PROPER(SUBSTITUTE(A1,"'","zzz")),"zzz","'") & "(")-1)

    (the trick is to insure the FIND() function cannot fail.)