This works for your posted example bit I think on balance I prefer the UDF <G
=MID(MID(MID(SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))-1),1,256),FIND("^",SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))-1)),256),2,FIND(" ",MID(MID(SUBSTITUTE(A1,"
","^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1),1,256),FIND("^",SUBSTITUTE(A1,"
","^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1)),256))-2)&MID(SUBSTITUTE(A1,"
","^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))),FIND("^",SUBSTITUTE(A1,"
","^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,256)
Mike
" wrote:
sample field:
HYDROXYZINE PAM TYA** 2 DOE, JOHN
sample output:
DOE, JOHN
I would like to pull out the name from the field above. Cutting point
should be at the nearest space preceding the last comma.
I'm having trouble wrapping my brain around it. I'd prefer to do it
with functions rather than VB script, but will be glad with either
solution.
Your help is very appreciated.
--T