Format Name

  • Thread starter Thread starter Dax Arroway
  • Start date Start date
D

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.
 
maybe this in b1 and dragged down

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

Mike
 
One way:

=PROPER(TRIM(MID(A1,FIND(",",A1)+1,255) & " " &
LEFT(A1,FIND(",",A1)-1)))
 
Thanks Mike but the first letter of the last name also goes lower case. How
do I get the first letter to remain capitalized?
 
It's all to do with spaces, see your other reponse where spaces are trimmed
out.

Mike
 
PERFECT! Thanks so much! You guys rock. I'd have never figured that out on
my own.
 
If you come back to this thread, here is a shorter alternative method...

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

Rick
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Conditional Formatting with Partial name search 2
Excel Comparing two columns 1
Formula for names 6
parsing a full name out into Last, First, Middle 2
breaking a field out 2
parsing name in cell by comma? 2
If..The Statement to automatically format a name 9
Lookup based on two fields without merging? 5

Back
Top