Separate text will be in other cell

Dear Sir / Madam,

Is there any way to extract the text to the other column. thank you for the help.

IMHO, any solution will have lot of possible errors.

Input is very inconsistent.

For example, it would be easier if DR. is always with .
In your case you have sometimes DR without .

Clinic is in some cases separated with - in some with ( and if in case clinic appear with space then it would be impossible to determine what is doctor name and what is clinic.

First you should work on consistent entry.

This could work for doctor (with . ) and clinic:

=TEXTAFTER(LOOKUP(REPT("Z",255),$D$6:$D7),"DR.")

This could work for clinic:

=TRIM(MID(LOOKUP(REPT("Z",255),$D$6:$D7),4+MIN(SEARCH(CHAR(SUBSTITUTE(ROW($A$33:$A$47),42,33)),SUBSTITUTE(LOOKUP(REPT("Z",255),$D$6:$D7),"DR.","")&CHAR(SUBSTITUTE(ROW($A$33:$A$47),42,33)))),255))

Need more time to figure out how to extract just doctor name...
Plus "if theres no clinic will be result N/A" part.. I got blank for now...

Thanks Mr. Zbor for the help sir, i know the data is frustrating and inconsistent sorry for that . But any solution even theres error , are accepted. Thank you god bless you all . .

I've also made this UDF so you can test it:


Use 1 to extract Doctor and 2 to extract Clinic


in E7:

=ExtractName(LOOKUP(REPT("Z",255), $D$6:$D7), 1)

in F7:

=ExtractName(LOOKUP(REPT("Z",255), $D$6:$D7), 2)

Extending formula down is hard due to merged cells...