Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Generate Text String | Excel Worksheet Functions | |||
Extracting and using Text from external sources | Excel Worksheet Functions | |||
EXTRACTING TEXT | Excel Discussion (Misc queries) | |||
Formating a text string? | Excel Discussion (Misc queries) | |||
Newbie: How to search a text string from right | Excel Worksheet Functions |