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

Maybe this two step approach...

B1:

=IF(ISNUMBER(FIND(" PA ",A1&" ")),TRIM(SUBSTITUTE(LEFT(A1,FIND(" PA
",A1&" ")-1),",","")),"")

C1:

=IF(B1<"",MID(B1,SEARCH("^^",SUBSTITUTE(B1,"
","^^",LEN(B1)-LEN(SUBSTITUTE(B1," ",""))))+1,1024),"")

Unfortunately, when the city's name is made up of two or more words, the
formula only returns the last one. However, if you know that the city
will be one of any number of known cities, the following approach might
be another possibility...

=IF(ISNUMBER(FIND(" PA ",A1&"
")),INDEX(D1:D10,MATCH(TRUE,INDEX(ISNUMBER(SEARCH( D1:D10,A1)),0),0)),"")

....where D1:D10 contains your list of known cities for which to search.

Hope this helps!

In article ,
BorisS wrote:

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