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

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

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

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

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

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

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