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