sort names by last name

[dshafique]

Hi guys, i have a set of data where the whole name is one cell, including the middle name. I want a formula which will organize it by last name, first name middle name. I have one formula which does something similar, but it gives me an error for cells that contain one word, like vacant, or something/guest

here's the formula i have

=MID(A2&", "&A2,FIND(" ",A2,FIND(" ",A2)+COUNTIF(A2,"* * *"))+1,LEN(A2)+1)

 

[zone709]

Try this

=IF(ISNUMBER(SEARCH(" ",A2))=TRUE,RIGHT(A2,LEN(A2)-FIND(" ",A2))& " " & LEFT(A2,FIND(" ",A2)-1),A2)

 

[dshafique]

Thanks! works perfectly!

 

[zone709]

​Cool

 

[AlKey]

Try this

=IF(ISNUMBER(SEARCH(" ",A2))=TRUE,RIGHT(A2,LEN(A2)-FIND(" ",A2))& " " & LEFT(A2,FIND(" ",A2)-1),A2)

You can remove this =TRUE. The ISNUMBER will return TRUE or FALSE.

 

[JackDanIce]

You can use IFERROR instead of IF:

=IFERROR(MID(A2,FIND(" ",A2),LEN(A2))&LEFT(A2,FIND(" ",A2)),A2)

 

[drgerry]

This is a good formula, but I would like to add ; at the end. I can't find where to put &";" ?????
I need Last, First M;

Don't as why . . . . . . long story . . . .

 

[Peter_SSs]

This is a good formula,..

Which one - there have been several suggested.

Could we have a small set of sample data and your expected results so we can see exactly what you have and what you want?
Might you have Last Names consisting of more than one word like "de Bruyn" or "van der Halen"?

 

[drgerry]

I have names as First M Last (some don't have M, so there will be a " " there)
Here is the formula I've been using and I figured out how to add the ";" at the end:
=MID(A2&", "&A2,FIND(" ",A2,FIND(" ",A2)+COUNTIF(A2,"* * *"))+1,LEN(A2)+1)&";"

Result:
First M Last - returns - Last, First M; (just what I wanted)
But, First "space" Last - returns - "space"Last, First"space";

 

[Peter_SSs]

Well, you could try this - but you didn't answer my last question.

<b>Re-Order Names</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:110px;" /><col style="width:123px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; ">First M Last</td><td style="font-size:10pt; ">Last, First M;</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; ">First   Last</td><td style="font-size:10pt; ">Last, First;</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B2</td><td >=TRIM(MID<span style=' color:008000; '>(SUBSTITUTE<span style=' color:#0000ff; '>(A2&", "&A2,"   ","  ")</span>,FIND<span style=' color:#0000ff; '>(" ",A2,FIND<span style=' color:#ff0000; '>(" ",A2)</span>+1)</span>+1,LEN<span style=' color:#0000ff; '>(A2)</span>+1)</span>)&";"</td></tr></table></td></tr></table>