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)


"Melissa" wrote in message
...
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" wrote:

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


"Melissa" wrote in message
...
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)

"Gary's Student" wrote:

I am assuming that you are starting with names like:

John Michael Smith
Victor Laszo

First use Data Text to Columns to split the field into three or
two
separate cells.

Say the name are in column A, and the first name is in column B,
column
C
will either contain the middle or last name. Column D will contain
the
last
name if there is a middle name.

Use the IF function to see if the cell in D is empty.

if D is empty then use =C1 & ", " & B1
If D is not empty then use =D1 & ", " & B1 & " " & C1
--
Gary's Student


"Melissa" wrote:

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.