|
separating first and last names
I have a column with a variety of first and last name variations:
Joe Smith Joe John Jones Tom Jack Stuart Apple Maria Lois Lane Johann Sebastian Bach I need to be able to sort them on their last name. Note that there is no comma separating their names and that there can be any number of names, HOWEVER, the last name always appears LAST. Probably, in order to make some sort of order out of this mess, it would be best if I create two separate columns: first names, last name. Any help will be appreciated. -- cheers, Tom G |
separating first and last names
=MID(A2,FIND("~",SUBSTITUTE(A2," ","~",LEN(A2)-LEN(SUBSTITUTE(A2,"
",""))))+1,255) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Tom G." wrote in message ... I have a column with a variety of first and last name variations: Joe Smith Joe John Jones Tom Jack Stuart Apple Maria Lois Lane Johann Sebastian Bach I need to be able to sort them on their last name. Note that there is no comma separating their names and that there can be any number of names, HOWEVER, the last name always appears LAST. Probably, in order to make some sort of order out of this mess, it would be best if I create two separate columns: first names, last name. Any help will be appreciated. -- cheers, Tom G |
separating first and last names
I am receiving an #VALUE error.
Thanks bob, from what I can understand, you are doing a "find" for the ~, however, this character does not exist. Am I missing something. I am looking to always use the last word in the column (there is never a comma delineating the different names - only text). TIA. "Bob Phillips" wrote in message ... =MID(A2,FIND("~",SUBSTITUTE(A2," ","~",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,255) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Tom G." wrote in message ... I have a column with a variety of first and last name variations: Joe Smith Joe John Jones Tom Jack Stuart Apple Maria Lois Lane Johann Sebastian Bach I need to be able to sort them on their last name. Note that there is no comma separating their names and that there can be any number of names, HOWEVER, the last name always appears LAST. Probably, in order to make some sort of order out of this mess, it would be best if I create two separate columns: first names, last name. Any help will be appreciated. -- cheers, Tom G |
separating first and last names
The line broke in the wrong spot.
Maybe... =MID(A2,FIND("~",SUBSTITUTE(A2," ","~", LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,255) (The =substitute() actually inserts the ~ character.) "Tom G." wrote: I am receiving an #VALUE error. Thanks bob, from what I can understand, you are doing a "find" for the ~, however, this character does not exist. Am I missing something. I am looking to always use the last word in the column (there is never a comma delineating the different names - only text). TIA. "Bob Phillips" wrote in message ... =MID(A2,FIND("~",SUBSTITUTE(A2," ","~",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,255) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Tom G." wrote in message ... I have a column with a variety of first and last name variations: Joe Smith Joe John Jones Tom Jack Stuart Apple Maria Lois Lane Johann Sebastian Bach I need to be able to sort them on their last name. Note that there is no comma separating their names and that there can be any number of names, HOWEVER, the last name always appears LAST. Probably, in order to make some sort of order out of this mess, it would be best if I create two separate columns: first names, last name. Any help will be appreciated. -- cheers, Tom G -- Dave Peterson |
separating first and last names
Having seen ur posting, I was trying to get it solved.. but couldnt
extrace the last name. Then i searched the this group and found one result.. this will give u the last name.. =MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255) But when i came here, bob had already posted one.. i couldnt find any real difference, but I too got an error from his copy.. I donno why.. Now I have to figure out how it works.. for the first name u can use =LEFT(A1,SEARCH(" ",A1)-1) Also u can use =TRIM(A1) to cleanup the name before extracting the same, although these works fine even without it... HTH Joe Tom G. wrote: I am receiving an #VALUE error. Thanks bob, from what I can understand, you are doing a "find" for the ~, however, this character does not exist. Am I missing something. I am looking to always use the last word in the column (there is never a comma delineating the different names - only text). TIA. "Bob Phillips" wrote in message ... =MID(A2,FIND("~",SUBSTITUTE(A2," ","~",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,255) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Tom G." wrote in message ... I have a column with a variety of first and last name variations: Joe Smith Joe John Jones Tom Jack Stuart Apple Maria Lois Lane Johann Sebastian Bach I need to be able to sort them on their last name. Note that there is no comma separating their names and that there can be any number of names, HOWEVER, the last name always appears LAST. Probably, in order to make some sort of order out of this mess, it would be best if I create two separate columns: first names, last name. Any help will be appreciated. -- cheers, Tom G |
separating first and last names
Slightly different formula, however with exactly the same concept:
=LEFT(A1,FIND("^^^",SUBSTITUTE(A1," ","^^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) The line wrap should be different here. The only 2 spaces in this formula are between the quotes that follow A1. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Tom G." wrote in message ... I am receiving an #VALUE error. Thanks bob, from what I can understand, you are doing a "find" for the ~, however, this character does not exist. Am I missing something. I am looking to always use the last word in the column (there is never a comma delineating the different names - only text). TIA. "Bob Phillips" wrote in message ... =MID(A2,FIND("~",SUBSTITUTE(A2," ","~",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,255) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Tom G." wrote in message ... I have a column with a variety of first and last name variations: Joe Smith Joe John Jones Tom Jack Stuart Apple Maria Lois Lane Johann Sebastian Bach I need to be able to sort them on their last name. Note that there is no comma separating their names and that there can be any number of names, HOWEVER, the last name always appears LAST. Probably, in order to make some sort of order out of this mess, it would be best if I create two separate columns: first names, last name. Any help will be appreciated. -- cheers, Tom G |
separating first and last names
one more thing..
U need to use TRIM() if there is space after the last name otherwise, u will not get result.. Joe_Germany wrote: Having seen ur posting, I was trying to get it solved.. but couldnt extrace the last name. Then i searched the this group and found one result.. this will give u the last name.. =MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255) But when i came here, bob had already posted one.. i couldnt find any real difference, but I too got an error from his copy.. I donno why.. Now I have to figure out how it works.. for the first name u can use =LEFT(A1,SEARCH(" ",A1)-1) Also u can use =TRIM(A1) to cleanup the name before extracting the same, although these works fine even without it... HTH Joe Tom G. wrote: I am receiving an #VALUE error. Thanks bob, from what I can understand, you are doing a "find" for the ~, however, this character does not exist. Am I missing something. I am looking to always use the last word in the column (there is never a comma delineating the different names - only text). TIA. "Bob Phillips" wrote in message ... =MID(A2,FIND("~",SUBSTITUTE(A2," ","~",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,255) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Tom G." wrote in message ... I have a column with a variety of first and last name variations: Joe Smith Joe John Jones Tom Jack Stuart Apple Maria Lois Lane Johann Sebastian Bach I need to be able to sort them on their last name. Note that there is no comma separating their names and that there can be any number of names, HOWEVER, the last name always appears LAST. Probably, in order to make some sort of order out of this mess, it would be best if I create two separate columns: first names, last name. Any help will be appreciated. -- cheers, Tom G |
separating first and last names
Or maybe not...
=LEFT(A1,FIND("^^^",SUBSTITUTE(A1," ","^^^", LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) <bg Ragdyer wrote: Slightly different formula, however with exactly the same concept: =LEFT(A1,FIND("^^^",SUBSTITUTE(A1," ","^^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) The line wrap should be different here. The only 2 spaces in this formula are between the quotes that follow A1. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Tom G." wrote in message ... I am receiving an #VALUE error. Thanks bob, from what I can understand, you are doing a "find" for the ~, however, this character does not exist. Am I missing something. I am looking to always use the last word in the column (there is never a comma delineating the different names - only text). TIA. "Bob Phillips" wrote in message ... =MID(A2,FIND("~",SUBSTITUTE(A2," ","~",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,255) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Tom G." wrote in message ... I have a column with a variety of first and last name variations: Joe Smith Joe John Jones Tom Jack Stuart Apple Maria Lois Lane Johann Sebastian Bach I need to be able to sort them on their last name. Note that there is no comma separating their names and that there can be any number of names, HOWEVER, the last name always appears LAST. Probably, in order to make some sort of order out of this mess, it would be best if I create two separate columns: first names, last name. Any help will be appreciated. -- cheers, Tom G -- Dave Peterson |
separating first and last names
Doesn't break that way in my O.E.
Picks the space between the quotes.<g But, maybe should emphasize to paste into the formula bar, and if a line wrap occurs while there is still room visible on the first line, place cursor at end of first line and "delete", so that first line fills. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Dave Peterson" wrote in message ... Or maybe not... =LEFT(A1,FIND("^^^",SUBSTITUTE(A1," ","^^^", LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) <bg Ragdyer wrote: Slightly different formula, however with exactly the same concept: =LEFT(A1,FIND("^^^",SUBSTITUTE(A1," ","^^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) The line wrap should be different here. The only 2 spaces in this formula are between the quotes that follow A1. -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Tom G." wrote in message ... I am receiving an #VALUE error. Thanks bob, from what I can understand, you are doing a "find" for the ~, however, this character does not exist. Am I missing something. I am looking to always use the last word in the column (there is never a comma delineating the different names - only text). TIA. "Bob Phillips" wrote in message ... =MID(A2,FIND("~",SUBSTITUTE(A2," ","~",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,255) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Tom G." wrote in message ... I have a column with a variety of first and last name variations: Joe Smith Joe John Jones Tom Jack Stuart Apple Maria Lois Lane Johann Sebastian Bach I need to be able to sort them on their last name. Note that there is no comma separating their names and that there can be any number of names, HOWEVER, the last name always appears LAST. Probably, in order to make some sort of order out of this mess, it would be best if I create two separate columns: first names, last name. Any help will be appreciated. -- cheers, Tom G -- Dave Peterson |
separating first and last names
Oh gosh ! ! !
Went right over my head. Pasted the darn wrong formula ... didn't I? =RIGHT(A1,LEN(A1)-SEARCH("^^^",SUBSTITUTE(A1," ","^^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) Still ... not a bad idea about filling the first line in the formula bar.<g -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Ragdyer" wrote in message ... Doesn't break that way in my O.E. Picks the space between the quotes.<g But, maybe should emphasize to paste into the formula bar, and if a line wrap occurs while there is still room visible on the first line, place cursor at end of first line and "delete", so that first line fills. -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Dave Peterson" wrote in message ... Or maybe not... =LEFT(A1,FIND("^^^",SUBSTITUTE(A1," ","^^^", LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) <bg Ragdyer wrote: Slightly different formula, however with exactly the same concept: =LEFT(A1,FIND("^^^",SUBSTITUTE(A1," ","^^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) The line wrap should be different here. The only 2 spaces in this formula are between the quotes that follow A1. -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Tom G." wrote in message ... I am receiving an #VALUE error. Thanks bob, from what I can understand, you are doing a "find" for the ~, however, this character does not exist. Am I missing something. I am looking to always use the last word in the column (there is never a comma delineating the different names - only text). TIA. "Bob Phillips" wrote in message ... =MID(A2,FIND("~",SUBSTITUTE(A2," ","~",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,255) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Tom G." wrote in message ... I have a column with a variety of first and last name variations: Joe Smith Joe John Jones Tom Jack Stuart Apple Maria Lois Lane Johann Sebastian Bach I need to be able to sort them on their last name. Note that there is no comma separating their names and that there can be any number of names, HOWEVER, the last name always appears LAST. Probably, in order to make some sort of order out of this mess, it would be best if I create two separate columns: first names, last name. Any help will be appreciated. -- cheers, Tom G -- Dave Peterson |
separating first and last names
On Sun, 3 Sep 2006 15:39:24 +0300, "Tom G." wrote:
I have a column with a variety of first and last name variations: Joe Smith Joe John Jones Tom Jack Stuart Apple Maria Lois Lane Johann Sebastian Bach I need to be able to sort them on their last name. Note that there is no comma separating their names and that there can be any number of names, HOWEVER, the last name always appears LAST. Probably, in order to make some sort of order out of this mess, it would be best if I create two separate columns: first names, last name. Any help will be appreciated. Have you tried "Text to Columns" under Data? Select only the cells containing the data you want to modify, then choose "Text to Columns" under "Data". If your data is formatted consistently, this should do what you want to do. Tip: if you have a last name like "van Patten", you can change the space between the 2 words to an underscore, and Excel will treat that entire string (van_Patten) as a single word. Then after you've finished moving the data, do a global search and replace to change underscores back into single spaces. Tom -- remove .spoo to reply by email |
| All times are GMT +1. The time now is 05:51 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com