Breaking Data in one column to two
Posted by Edie on February 14, 2002 1:22 PM
I have a large mailing list in Excel. I receive other lists that have the names in only one column.
I am having trouble breaking up names in one column to two columns.
I need a formula to break up to First name with initial and Last name with Jr, or M.D. ect
Than I will use copy and special past to freeze the values.
I have tried to use search and left formulas but cannot pick up the initial or ending name.
I would also like to delete the comas and periods. Is there a formula for that?
Examples:
Anton P. Hupa, Jr.
Antonio N. Otto, Jr.
Arthur Barth
Arthur Schulz
Arthur P. Tonn
Arthur C. White
Arthur G. Snow
Art S. Dhinsa, M.D.
Badr A. Ishake
Barbara Jean Gillis
Barbara V. Pendle
Barbara D. Mallon
Barbara G. Fad
Barbara A. Allison
Barbara C. Sharp
Ben Tomaszew
Ben Houser
Ben Dunlap
Bennie P. Roskow
Bernard F. Hall, M.D.
Bernard T. Leon, Ph.D.
Bert A. Farnam, Jr.
Bill Wettsteine
Any help would be appreciated!
Thank you,
Edie Hahn
Posted by Mark W. on February 14, 2002 1:27 PM
Did you see Aladin's EXCELLENT advice at...
Posted by Mark W. on February 14, 2002 1:34 PM
Once you've extracted the last name, etc...
...into cell B1 (for example) you can use
=LEFT(A1,FIND(B1,A1)-1) to get the first name
and middle initial. In the formula A1 contains
the original name and B1 contains the last name,
etc.
Posted by Edie on February 14, 2002 1:41 PM
Re: Once you've extracted the last name, etc...
I am getting a Circular Reference error with that formula?
Posted by Mark W. on February 14, 2002 1:45 PM
Re: Once you've extracted the last name, etc...
Where did you enter the formula? Also, small
revision to my suggestion...
=TRIM(LEFT(A1,FIND(B1,A1)-1))
...to get rid of the space character(s) before
the last name.
Posted by Edie on February 14, 2002 1:53 PM
Re: Once you've extracted the last name, etc...
B1 I also tried it in C1 just to see if I had it wrong and nothing but the formula was in c1 cell Where did you enter the formula? Also, small
Posted by Mark W. on February 14, 2002 2:21 PM
Okay, let me try again...
Use =TRIM(LEFT(ref1,FIND(ref2,ref1)-1))
where "ref1" is the reference to the cell
containing your original name (i.e.,
first MI last, title) and "ref2" is the
reference to the cell containing Aladin's
formula which also references "ref1".
B1 I also tried it in C1 just to see if I had it wrong and nothing but the formula was in c1 cell : Where did you enter the formula? Also, small
Posted by Aladin Akyurek on February 14, 2002 2:37 PM
Mark & Edie: An Adaptation of...
to Edie's situation:
Assuming that the first target-name string is in A1,
in B1 enter: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,", ","#"),RIGHT(G1,LEN(SUBSTITUTE(A1,", ","#"))-SEARCH("@",SUBSTITUTE(SUBSTITUTE(A1,", ","#")," ","@",LEN(SUBSTITUTE(A1,", ","#"))-LEN(SUBSTITUTE(SUBSTITUTE(A1,", ","#")," ",""))))),""),".","")
in C1 enter: =SUBSTITUTE(SUBSTITUTE(RIGHT(SUBSTITUTE(A1,", ","#"),LEN(SUBSTITUTE(SUBSTITUTE(A1,", ","#"),", ","#"))-SEARCH("@",SUBSTITUTE(SUBSTITUTE(A1,", ","#")," ","@",LEN(SUBSTITUTE(A1,", ","#"))-LEN(SUBSTITUTE(SUBSTITUTE(A1,", ","#")," ",""))))),"#"," "),".","")
Select B1:C1 and drag down as far as needed.
I know these are gigantic formulas, but Edie wants lots of things to happen to the target strings.
Anyway, this is what you get in B:C wrt to the sample provided:
{"Anton P ","Hupa Jr";
"Antonio N ","Otto Jr";
"Arthur ","Barth";
"Arthur ","Schulz";
"Arthur P ","Tonn";
"Arthur C ","White";
"Arthur G ","Snow";
"Art S ","Dhinsa MD";
"Badr A ","Ishake";
"Barbara Jean ","Gillis";
"Barbara V ","Pendle";
"Barbara D ","Mallon";
"Barbara G ","Fad";
"Barbara A ","Allison";
"Barbara C ","Sharp";
"Ben ","Tomaszew";
"Ben ","Houser";
"Ben ","Dunlap";
"Bennie P ","Roskow";
"Bernard F ","Hall MD";
"Bernard T ","Leon PhD";
"Bert A ","Farnam Jr";
"Bill ","Wettsteine"}
Aladin
Posted by Edie on February 14, 2002 3:03 PM
Re: Mark & Edie: An Adaptation of...
Wonderful it worked! In Cell B I got the names with out the periods and commas and in Cell C I got the Last name.
Thank you very much there is on way I would have been able to figure out that formula!
Is there a site that shows examples of formulas for problems like this?
Thanks again to you both! I am sure glad I found this site!
Edie
