Separate name and numbers

[SherryScrapDog]

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]

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&"0123456789"))-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]

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

 

[T. Valko]

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&"0123456789")),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 Edit>Copy
Then, Edit>Paste Special>Values>OK

Now you can get rid of the original data.

 

[bosco_yip]

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&"0123456789")),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&1234567890)),255)

and gave the same result

Regards
Bosco

 

[SherryScrapDog]

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

 

[T. Valko]

You're welcome. Thanks for the feedback!