Format Name

[Dax Arroway]

Can someone please help with a formula?

In Column A I have names formatted as SMITH, John. In Column B I need John
Smith. Would someone please help with a formula that would switch the names,
get rid of the comma, and change the case to lower case of the last name
except for the first letter?

Thanks in advance!
- D.

 

[Mike H]

maybe this in b1 and dragged down

=MID(A1,FIND(",",A1,1)+2,LEN(A1))&" "&LOWER(LEFT(A1,FIND(",",A1,1)-1))

Mike

 

[JE McGimpsey]

One way:

=PROPER(TRIM(MID(A1,FIND(",",A1)+1,255) & " " &
LEFT(A1,FIND(",",A1)-1)))

 

[Dax Arroway]

Thanks Mike but the first letter of the last name also goes lower case. How
do I get the first letter to remain capitalized?

 

[Mike H]

It's all to do with spaces, see your other reponse where spaces are trimmed
out.

Mike

 

[Dax Arroway]

PERFECT! Thanks so much! You guys rock. I'd have never figured that out on
my own.

 

[Rick Rothstein \(MVP - VB\)]

If you come back to this thread, here is a shorter alternative method...

=PROPER(MID(A7&" "&A7,FIND(" ",A7)+1,LEN(A7)-1))

Rick