Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract name before comma, space & number
I have a column of names that also have page number(s). Here are some
examples: Mary, 155 Joe, Rev., 60 Mark, Mrs., 115, 120, 130 There could be many page numbers. I have a working formula for getting the page numbers into a separate column. I can't find one that gets the Joe, Rev. (for example); I only get Joe. Can you help? Thanks if you can!! Sherry |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract name before comma, space & number
On Sun, 25 Jan 2009 17:17:02 -0800, SherryScrapDog
wrote: I have a column of names that also have page number(s). Here are some examples: Mary, 155 Joe, Rev., 60 Mark, Mrs., 115, 120, 130 There could be many page numbers. I have a working formula for getting the page numbers into a separate column. I can't find one that gets the Joe, Rev. (for example); I only get Joe. Can you help? Thanks if you can!! Sherry *IF* every name is followed by <comma<space<digit, and IF there are NO digits in the name, then: =LEFT(A1,MIN(SEARCH({1,2,3,4,5,6,7,8,9,0},A1&{1,2, 3,4,5,6,7,8,9,0}))-3) If the variations in your column is more complex, then post back with more information. --ron |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract name before comma, space & number
Thanks Ron!! Perfect! I keep trying to understand these by reading all of
the various other posts and just couldn't quite do it. Much appreciation, Sherry "Ron Rosenfeld" wrote: On Sun, 25 Jan 2009 17:17:02 -0800, SherryScrapDog wrote: I have a column of names that also have page number(s). Here are some examples: Mary, 155 Joe, Rev., 60 Mark, Mrs., 115, 120, 130 There could be many page numbers. I have a working formula for getting the page numbers into a separate column. I can't find one that gets the Joe, Rev. (for example); I only get Joe. Can you help? Thanks if you can!! Sherry *IF* every name is followed by <comma<space<digit, and IF there are NO digits in the name, then: =LEFT(A1,MIN(SEARCH({1,2,3,4,5,6,7,8,9,0},A1&{1,2, 3,4,5,6,7,8,9,0}))-3) If the variations in your column is more complex, then post back with more information. --ron |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract name before comma, space & number
On Sun, 25 Jan 2009 18:05:00 -0800, SherryScrapDog
wrote: Thanks Ron!! Perfect! I keep trying to understand these by reading all of the various other posts and just couldn't quite do it. Much appreciation, Sherry Glad to help. Thanks for the feedback. And reading and trying to understand how the various suggestions work is how I've advanced my knowledge -- sometimes painfully slowly. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time exported w/ space/comma, Convert to Number | Excel Worksheet Functions | |||
Extract the text between last comma and last but one comma. | Excel Worksheet Functions | |||
add extra space before comma | Excel Worksheet Functions | |||
Inserting a space after a comma | Excel Worksheet Functions | |||
Removing a space after a comma | New Users to Excel |