RIGHT & MID & LEFT

[Guest]

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.

 

[Guest]

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)

 

[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)

 

[Guest]

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?

 

[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)