|
Separate name and numbers
I have a column with names and number(s) in them and want to end up with the
name in one column and the number(s) in another column. The first names may actually be just one name or it could include middle and/or title. The number may be 1 or more numbers (these are page number(s) for this name). Here are a few examples: S. J. 45, 46, 59, 71, 138, 139 S. J., Mrs. 138 Samuel J. 51 Sally 3 W. 41 Warren 17, 61, 71, 95, 109 I want 2 columns: Name Page S. J. 45, 46, 59, 71, 138, 139 Samuel J. 51 Sally 3 W. 41 Warren 17, 61 71, 95, 109 Can this be done? thanks in advance if you can help, Sherry |
Separate name and numbers
Adapted from an earlier post
If you have your names+numbers in Col A and assuming a blank between last letter and first number then enter this in B1 =LEFT(A1,MIN(FIND({0;1;2;3;4;5;6;7;8;9},A1&"012345 6789"))-2) and copy down and this in C1 =RIGHT(A1,LEN(A1)+1-MIN(FIND({0;1;2;3;4;5;6;7;8;9},A1&"0123456789"))) and copy down "SherryScrapDog" wrote: I have a column with names and number(s) in them and want to end up with the name in one column and the number(s) in another column. The first names may actually be just one name or it could include middle and/or title. The number may be 1 or more numbers (these are page number(s) for this name). Here are a few examples: S. J. 45, 46, 59, 71, 138, 139 S. J., Mrs. 138 Samuel J. 51 Sally 3 W. 41 Warren 17, 61, 71, 95, 109 I want 2 columns: Name Page S. J. 45, 46, 59, 71, 138, 139 Samuel J. 51 Sally 3 W. 41 Warren 17, 61 71, 95, 109 Can this be done? thanks in advance if you can help, Sherry |
Separate name and numbers
Sheeloo... THANKS!! This will save me so much work. It worked perfectly. I
was reading the other posts and just do not know enough to figure out how to apply to my problem. Much appreciation! Sherry "Sheeloo" wrote: Adapted from an earlier post If you have your names+numbers in Col A and assuming a blank between last letter and first number then enter this in B1 =LEFT(A1,MIN(FIND({0;1;2;3;4;5;6;7;8;9},A1&"012345 6789"))-2) and copy down and this in C1 =RIGHT(A1,LEN(A1)+1-MIN(FIND({0;1;2;3;4;5;6;7;8;9},A1&"0123456789"))) and copy down "SherryScrapDog" wrote: I have a column with names and number(s) in them and want to end up with the name in one column and the number(s) in another column. The first names may actually be just one name or it could include middle and/or title. The number may be 1 or more numbers (these are page number(s) for this name). Here are a few examples: S. J. 45, 46, 59, 71, 138, 139 S. J., Mrs. 138 Samuel J. 51 Sally 3 W. 41 Warren 17, 61, 71, 95, 109 I want 2 columns: Name Page S. J. 45, 46, 59, 71, 138, 139 Samuel J. 51 Sally 3 W. 41 Warren 17, 61 71, 95, 109 Can this be done? thanks in advance if you can help, Sherry |
Separate name and numbers
Try this...
Assume your data is in the range A2:A7. Enter this formula in C2: =MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456 789")),255) Enter this formula in B2: =TRIM(SUBSTITUTE(A2,C2,"")) Select both B2 and C2 then copy dow to row 7. If you want to get rid of the original data before you do you need to convert the formulas to constants. Select the entire range of formulas Goto the menu EditCopy Then, EditPaste SpecialValuesOK Now you can get rid of the original data. -- Biff Microsoft Excel MVP "SherryScrapDog" wrote in message ... I have a column with names and number(s) in them and want to end up with the name in one column and the number(s) in another column. The first names may actually be just one name or it could include middle and/or title. The number may be 1 or more numbers (these are page number(s) for this name). Here are a few examples: S. J. 45, 46, 59, 71, 138, 139 S. J., Mrs. 138 Samuel J. 51 Sally 3 W. 41 Warren 17, 61, 71, 95, 109 I want 2 columns: Name Page S. J. 45, 46, 59, 71, 138, 139 Samuel J. 51 Sally 3 W. 41 Warren 17, 61 71, 95, 109 Can this be done? thanks in advance if you can help, Sherry |
Separate name and numbers
A1 =S. J. 45, 46, 59, 71, 138, 139
To extract the number in a string+number, Cell B1 entered the formula ( wrote by Biff ) : =MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456 789")),255) This part of the formula returned the 1st postion of the number ( 7 ) : MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")) If you took away the " from the formula MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&0123456789)) The formula returned #VALUE! , and look like this MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&123456789)) because, EXCEL removed the zero automatically But, if you took away the " , and placed the zero behind of any digits ( 1 to 9 ) something like : MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&1234567890)) The formula gave the correct result 7 Finally, the formula could be written in : =MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&12345678 90)),255) and gave the same result Regards Bosco "T. Valko" wrote: Try this... Assume your data is in the range A2:A7. Enter this formula in C2: =MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456 789")),255) Enter this formula in B2: =TRIM(SUBSTITUTE(A2,C2,"")) Select both B2 and C2 then copy dow to row 7. If you want to get rid of the original data before you do you need to convert the formulas to constants. Select the entire range of formulas Goto the menu EditCopy Then, EditPaste SpecialValuesOK Now you can get rid of the original data. -- Biff Microsoft Excel MVP "SherryScrapDog" wrote in message ... I have a column with names and number(s) in them and want to end up with the name in one column and the number(s) in another column. The first names may actually be just one name or it could include middle and/or title. The number may be 1 or more numbers (these are page number(s) for this name). Here are a few examples: S. J. 45, 46, 59, 71, 138, 139 S. J., Mrs. 138 Samuel J. 51 Sally 3 W. 41 Warren 17, 61, 71, 95, 109 I want 2 columns: Name Page S. J. 45, 46, 59, 71, 138, 139 Samuel J. 51 Sally 3 W. 41 Warren 17, 61 71, 95, 109 Can this be done? thanks in advance if you can help, Sherry |
Separate name and numbers
Biff and Bosco,
Thanks!!! This also worked and I have saved this too. I used Biff's and got perfect results (because my page numbers do not begin with 0). I really appreciate the explanation about the zero in case I get another file that might have a zero. I would love to learn how these functions really work and always have so much (volunteer) work to do, I ask here and you guys always come thru and allow me to get thru my next problem file. There are 11,000+ records in this file and I have 2 more coming that are both larger than this one. Many, many thanks! Sherry "bosco_yip" wrote: A1 =S. J. 45, 46, 59, 71, 138, 139 To extract the number in a string+number, Cell B1 entered the formula ( wrote by Biff ) : =MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456 789")),255) This part of the formula returned the 1st postion of the number ( 7 ) : MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")) If you took away the " from the formula MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&0123456789)) The formula returned #VALUE! , and look like this MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&123456789)) because, EXCEL removed the zero automatically But, if you took away the " , and placed the zero behind of any digits ( 1 to 9 ) something like : MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&1234567890)) The formula gave the correct result 7 Finally, the formula could be written in : =MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&12345678 90)),255) and gave the same result Regards Bosco "T. Valko" wrote: Try this... Assume your data is in the range A2:A7. Enter this formula in C2: =MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456 789")),255) Enter this formula in B2: =TRIM(SUBSTITUTE(A2,C2,"")) Select both B2 and C2 then copy dow to row 7. If you want to get rid of the original data before you do you need to convert the formulas to constants. Select the entire range of formulas Goto the menu EditCopy Then, EditPaste SpecialValuesOK Now you can get rid of the original data. -- Biff Microsoft Excel MVP "SherryScrapDog" wrote in message ... I have a column with names and number(s) in them and want to end up with the name in one column and the number(s) in another column. The first names may actually be just one name or it could include middle and/or title. The number may be 1 or more numbers (these are page number(s) for this name). Here are a few examples: S. J. 45, 46, 59, 71, 138, 139 S. J., Mrs. 138 Samuel J. 51 Sally 3 W. 41 Warren 17, 61, 71, 95, 109 I want 2 columns: Name Page S. J. 45, 46, 59, 71, 138, 139 Samuel J. 51 Sally 3 W. 41 Warren 17, 61 71, 95, 109 Can this be done? thanks in advance if you can help, Sherry |
Separate name and numbers
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "SherryScrapDog" wrote in message ... Biff and Bosco, Thanks!!! This also worked and I have saved this too. I used Biff's and got perfect results (because my page numbers do not begin with 0). I really appreciate the explanation about the zero in case I get another file that might have a zero. I would love to learn how these functions really work and always have so much (volunteer) work to do, I ask here and you guys always come thru and allow me to get thru my next problem file. There are 11,000+ records in this file and I have 2 more coming that are both larger than this one. Many, many thanks! Sherry "bosco_yip" wrote: A1 =S. J. 45, 46, 59, 71, 138, 139 To extract the number in a string+number, Cell B1 entered the formula ( wrote by Biff ) : =MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456 789")),255) This part of the formula returned the 1st postion of the number ( 7 ) : MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")) If you took away the " from the formula MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&0123456789)) The formula returned #VALUE! , and look like this MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&123456789)) because, EXCEL removed the zero automatically But, if you took away the " , and placed the zero behind of any digits ( 1 to 9 ) something like : MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&1234567890)) The formula gave the correct result 7 Finally, the formula could be written in : =MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&12345678 90)),255) and gave the same result Regards Bosco "T. Valko" wrote: Try this... Assume your data is in the range A2:A7. Enter this formula in C2: =MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456 789")),255) Enter this formula in B2: =TRIM(SUBSTITUTE(A2,C2,"")) Select both B2 and C2 then copy dow to row 7. If you want to get rid of the original data before you do you need to convert the formulas to constants. Select the entire range of formulas Goto the menu EditCopy Then, EditPaste SpecialValuesOK Now you can get rid of the original data. -- Biff Microsoft Excel MVP "SherryScrapDog" wrote in message ... I have a column with names and number(s) in them and want to end up with the name in one column and the number(s) in another column. The first names may actually be just one name or it could include middle and/or title. The number may be 1 or more numbers (these are page number(s) for this name). Here are a few examples: S. J. 45, 46, 59, 71, 138, 139 S. J., Mrs. 138 Samuel J. 51 Sally 3 W. 41 Warren 17, 61, 71, 95, 109 I want 2 columns: Name Page S. J. 45, 46, 59, 71, 138, 139 Samuel J. 51 Sally 3 W. 41 Warren 17, 61 71, 95, 109 Can this be done? thanks in advance if you can help, Sherry |
| All times are GMT +1. The time now is 02:34 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com