Did you see Aladin's EXCELLENT advice at...

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.

Re: Once you've extracted the last name, etc...

I am getting a Circular Reference error with that formula?

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.

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

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

Mark & Edie: An Adaptation of...

22330.html

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

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