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

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

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

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