upper/lower formula

[elena13c]

Hello,

I have a huge excel where the first and last names in a cell are like this : JAMES SMITH.

Could you help me please with a formula so I can have James SMITH?

Thank you in advance

 

[Vbagreenhorn1]

this is not the fastest way to do it and it will only work when names are like James Smith or John Doe.

Sub Elena()
For Each cl In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
cl.Value = Left(cl, 1) & LCase(Mid(cl, 2, InStr(cl, " ") - 2)) & " " & Right(cl, Len(cl) - InStr(cl, " "))
Next
End Sub

 

[Gerald Higgins]

Sorry, I misread the OP.

Try again

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

 

[elena13c]

It worked! Thank you very much!

 

[Rick Rothstein]

It worked! Thank you very much!

Here is a shorter formula which uses one less function call that you can also use...

=REPLACE(PROPER(A1)&A1,FIND(" ",A1),LEN(A1),"")