Phew, how about

=IF(ISNUMBER(FIND("PA",B2)),MID(SUBSTITUTE(B2,"PA" ,""),FIND("~",SUBSTITUTE(B
2," ","~",LEN(B2)-LEN(SUBSTITUTE(B2," ",""))-1))+1,99),"Non PA")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"BorisS" wrote in message
...
I have a string in a cell which is a full address string. I need to check
to
see if the address is a Pennsylvania address, and then if it is, I need to
find what the city listed is right before the PA. The way I am tryign to
do
it is as follows:

IF(ISERROR(FIND("PA",B2)),"Non PA",MID("PA",FIND("PA",B2),-10))

It's coming back with a VALUE!, and I am guessing becuase I am trying to
do
a negative MID function. How do I get it to find a place in the cell and
then go left a certain number of characters?

Thx.
--
Boris