Sorting column by last word (name)

**braenz** · 09-08-2008, 11:25 PM

Hi all

I have a column of names generated by my accounting program (Quickbooks), but the way Quickbook works means the names are sorted on their first entry which is usually the given name. Sometimes the names are husband and wife, and sometimes they may have two words making up their surname. Complicated!

I want to sort by surname - how could I do that?

e.g.

Jacqui Knight
Tom & Jerry Cat
Leonard da Vinci

Jacqui

**sglife** · 09-08-2008, 11:54 PM

this code only for your reference.
I think your data is too complicated to solve

Please Login or Register  to view this content.

**Trebor76** · 09-09-2008, 12:48 AM

Hi there,

Put the following formula (assuming the data starts at row 2 - change as required) in the first available blank column next your data fill down as necessary and then sort the entire data range by it. You can then delete the column if you wish:

=RIGHT(SUBSTITUTE(A2," ","~",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))),LEN(SUBSTITUTE(A2," ","~",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))-FIND("~",(SUBSTITUTE(A2," ","~",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))))

Note this formula was created by "Datsmart" from this thread.

HTH

Robert