Steven,

Just copy that select, then select all the others and past the formula in.
Excel will adjust to the appropriate rows.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Steven Deng" wrote in message
news:p%Whd.101146$%k.9555@pd7tw2no...
Ya ! Bob, Here is how it may look like.


="*" & MID(A1,FIND("~",(SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1,"
","")))))+1,99)&", "&LEFT(A1,FIND("~",(SUBSTITUTE(A1,"
","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))))

Also I am wondering, how can I apply this piece of function
on a list of cells instead of one cell (say the names are in the cells
A1:A3000), How can I achieve this without invoking writing macros?

Sincerely,
Steven Deng


"Bob Phillips" дÈëÏûÏ¢ÐÂÎÅ
...
Are you okay with that, or do you want help?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Steven Deng" wrote in message
news:UYRhd.97175$nl.83171@pd7tw3no...
Sweet ! Bob, Now I need to do is to modify it a little bit to get "*"
and
"," in place!

Sincerely
Steven Deng


"Bob Phillips" wrote in message
...
Steven,

Try this worksheet formula


--