Extracting Surname from within a text string
 

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.

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)


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

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.




In article ,
"Iainkerr01" wrote:

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.

Thank you. A very elegant solution which works perfectly.

"JE McGimpsey" wrote:

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.




In article ,
"Iainkerr01" wrote:

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.

Thank you. A very elegant solution which works perfectly.

"JE McGimpsey" wrote:

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.




In article ,
"Iainkerr01" wrote:

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.

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

"Bob Phillips" wrote:

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)


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

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

--

HTH

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


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

"Bob Phillips" wrote:

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)


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