RIGHT & MID & LEFT

M

Melissa

I have a spreadsheet with all names converted to Last Name, First Name.
Some, not all, of the names have a middle name as well. I have searched
postings and Microsoft help and cannot figure out how to return "Smith, John
Michael" if I have the name "John Michael Smith". Thank you in advance for
any help.
 
M

Melissa

I currently have the following formula for those names without a middle
name/initial. Is there not a way I can just add "MID" to it?

=RIGHT(A19,LEN(A19)-FIND(" ",A19))&", "&LEFT(A19,FIND(" ",A19)-1)
 
B

Bob Phillips

=IF(LEN(A19)-LEN(SUBSTITUTE(A19," ",""))>1,RIGHT(A19,LEN(A19)-FIND("
",A19,FIND(" ",A19)+1))&", "&LEFT(A19,FIND(" ",A19,FIND("
",A19)+1)-1),RIGHT(A19,LEN(A19)-FIND(" ",A19))&", "&LEFT(A19,FIND("
",A19)-1))

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
M

Melissa

Thank you.

When I tried this formula, and changed the "A19" to the correct cell, I
received a #VALUE error. I'm not familiar with many of the formulas in
excel. Any suggestions?
 
B

Bob Phillips

You need to be careful because it is so long. Copy it and paste it into the
formula bar, not the cell, and amend it there.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Top