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