Extracting Surname from within a text string

[Guest]

I have a spreadsheet column which stores client names in the format 'Mr & Mrs
Fred Bloggs' or Mr Iain Kerr.

I want to be able to sort this in alphabetic order based on the Surname
only. Is there a multi function to determine where the first space charactor
is from the Right Hand Side? This would give the the start position (-1) of
the Surname.

I figured that if I can isolate that into a hidden column, then I can easily
sort on it.

 

[Bob Phillips]

Iain,

This will get the position of the last space

=FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))

The surname then just becomes

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

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[JE McGimpsey]

One way:

=MID(A1,FIND("$", SUBSTITUTE(A1,"_","$", LEN(A1) -
LEN(SUBSTITUTE(A1,"_",""))))+1, 255)

where I've substituted underscores (_) for spaces to prevent unfortunate
linewrap.

 

[Guest]

Thank you. A very elegant solution which works perfectly.

 

[Guest]

Thank you. A very elegant solution which works perfectly.

 

[Guest]

Thank you gor your reply. It's interesting to compare your solution with the
second one. Both work perfectly though. Much appreciated.

 

[Bob Phillips]

I think they are exactly the same, just JE uses$ where I use ~

--

HTH

RP
(remove nothere from the email address if mailing direct)