Formula for sorting column by last name

[Bradex]

Cells have entries like this: First name, space, last name, city. E.g. John Doe, Chicago.

I wish to sort the column by the last name.

Is there a formula for this?

[alansidman]

Assume your data in Column A. In column B, (a helper column), insert the following and copy it down.

=MID(A1,FIND(" ",A1),FIND(",",A1)-FIND(" ",A1))

Then sort on the Column B

[Bradex]

Perfect!

Just one more question...

Assume there are other names like "Mary Elizabeth Smith, Chicago". In such cases, it would be better to use a formula which picks up the word preceding the last word. Could you please give such a formula?

[Bradex]

Found two of them elsewhere:

=TRIM(LEFT(RIGHT(SUBSTITUTE(" "&TRIM(A1)," ",REPT(" ",40)),80),40))

OR

=MID(A1,FIND("^",SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1))+1,FIND("^",SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-FIND("^",SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1))-1)

However, they take the word and include the comma. E.g. Smith,

Any suggestions to rectify this issue? Or, a better formula perhaps?

[AlKey]

Found two of them elsewhere:

=TRIM(LEFT(RIGHT(SUBSTITUTE(" "&TRIM(A1)," ",REPT(" ",40)),80),40))

OR

=MID(A1,FIND("^",SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1))+1,FIND("^",SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-FIND("^",SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1))-1)

However, they take the word and include the comma. E.g. Smith,

Any suggestions to rectify this issue? Or, a better formula perhaps?

I would not recommend to use the above formulas because they will fail if the the city contains more than one word; San Francisco, or Fort Lauderdale.

Use this one instead:

Please Login or Register  to view this content.

v	A	B
1	John Doe, Chicago	Doe
2	Mary Elizabeth Smith, Chicago	Smith
3	Mary J Dougan, San Francisco	Dougan
4	George H Bush, Dallas	Bush
5	George H W Bush, Fort Lauderdale	Bush

[Bradex]

That's perfect! Thanks a lot AlKey!

Could you also explain what this formula does in plain words.