Sorry to put one more wrinkle in, but is there a way to layer in the ability
for the cell to find the spot prior to "PA" that is a space, and then display
the next character until it reaches a comma?

Basically, what I have is a cell that contains within it (it's longer, but
I've cut off the front to make it slightly more visible here):

areth Road Easton, PA 18045

Is there a way for this formula to look at that whole cell (with a beginning
part of the text which varies in length) and come back with only "Easton", in
the case that the formula first finds a "PA" there? That would give me a
formula which tells me what I am looking for...any city in PA that is listed
somewhere in these addresses.

Thx again if this is doable. Sorry for the challenge.
--
Boris


"Bob Phillips" wrote:

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