Let's assume your string is in A1. For simplicity, I would suggest
using two formulas. One is to find the position of the comma:

B1: =LEN(A1)-MATCH(",",MID(A1,LEN(A1)+1-ROW(INDIRECT("1:"&LEN(A1))),1),
0)

Use Ctrl+Shift+Enter. Result for sample input is 28, which is the E
of DOE.

Next, to find the position of the space immediately preceding it (also
Ctrl+Shift+Enter):

C1: =MATCH(CHAR(222),MID(SUBSTITUTE(A1," ",CHAR(222),LEN(LEFT(A1,B1))-
LEN(SUBSTITUTE(LEFT(A1,B1)," ",""))),ROW(INDIRECT("1:"&LEN(A1))),1),0)

Finally, extract using RIGHT():

D1: =RIGHT(A1,LEN(A1)-C1)

It's kind of a brute-force solution, but it works. Calculation would
take about 0.6 seconds for 5000 records.

By the way, a single-cell way of doing exact same algorithm looks like
the following, but will only work in Excel 2007 due to function
nesting limitations of previous versions:

=RIGHT(A1,LEN(A1)-MATCH(CHAR(222),MID(SUBSTITUTE(A1,"
",CHAR(222),LEN(LEFT(A1,LEN(A1)-MATCH(",",MID(A1,LEN(A1)+1-
ROW(INDIRECT("1:"&LEN(A1))),1),0)))-LEN(SUBSTITUTE(LEFT(A1,LEN(A1)-
MATCH(",",MID(A1,LEN(A1)+1-ROW(INDIRECT("1:"&LEN(A1))),1),0)),"
",""))),ROW(INDIRECT("1:"&LEN(A1))),1),0))

Hope that helps.


On Feb 12, 3:19 pm, "
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