Capitalize First Letter of All Words and Disregard Rest

[dlc3172]

Hello-

I know how to use =PROPER to capitalize the first letter of each word in a cell and make the rest lowercase. But is there a way to capitalize the first letter of each word and leave everything else alone? For example, if I have:

first lAST

How can I end up with?:

First LAST

[AlKey]

Try this

=PROPER(LEFT(A1,FIND(" ",A1)))&UPPER(MID(A1,FIND(" ",A1)+1,10))

v	A	B
1	first lAST	First LAST

[6StringJazzer]

How many words are in your cells?

[dlc3172]

Try this

=PROPER(LEFT(A1,FIND(" ",A1)))&UPPER(MID(A1,FIND(" ",A1)+1,10))

v A B
1 first lAST First LAST





Thanks for the response.

Your formula seems to be capitalizing EVERY letter in any second word. In my example, "lAST" should indeed end up with all capital letters. But if the example used is "first lasT", it should end up as "First LasT". In other words, only the first letter of each word should be capitalized; every other letter that is not a first letter should be left as is.

[dlc3172]

>>>How many words are in your cells?



Some have only one word. Others have two or more.

[6StringJazzer]

I think doing this with a worksheet formula for an indefinite number of words would be very complex. Here is a VBA function to do it:

Please Login or Register  to view this content.

[dlc3172]

I think doing this with a worksheet formula for an indefinite number of words would be very complex. Here is a VBA function to do it:

Please Login or Register  to view this content.

Thanks - I need to keep this to a simple formula, though. I could limit this to no more than two words in a cell. Do you think a formula would work in that case?

[6StringJazzer]

This will work for one or two words:

Please Login or Register  to view this content.

Although Excel can do string manipulation, it is not very good at it.

[dlc3172]

This will work for one or two words:

Please Login or Register  to view this content.

Although Excel can do string manipulation, it is not very good at it.

Thanks. That works for me. I would have never figured this out, so many thanks.

[6StringJazzer]

Thanks for the rep--if your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

[Rick Rothstein]

Please Login or Register  to view this content.

The OP's needs appear pretty simple, but in a generalized case, a word could follow a punctuation (opening parenthesis, dot, dash, and some others). Here is a solution that accounts for that possibility...

Please Login or Register  to view this content.