On Fri, 9 Dec 2005 12:24:02 -0800, BorisS
wrote:

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.

Regular expressions can do this fairly simply.

I have assumed that the "City" is characterized by the comma-separated word or
words that precede the PA abbreviation.

In other words, your address string looks something like:

123 East Rd., your city name, PA 01234

The formula will display "your city name" regardless of how many words are in
there. There must be at least one space before "your city name".

If this is what you want, first download and install Longre's free morefunc.xll
add-in from http://xcell05.free.fr

Then use this formula:

=REGEX.MID(TRIM(A1),"(?<=,\s).*(?=,\s*PA)")

To explain:

TRIM(A1) so there will only be one space between words.

There are three parts of the expression that all have to be met:

,\s -- a comma followed by a <space

..* any number of characters

,\s*PA followed by a comma space PA

The ?<= and ?= are instructions that say even though we need this part of the
pattern to make a match, don't return it as a result of the function.

The PA could also be in a cell, with the cell reference substituted in the
formula. For example, if cell F1 contained PA, then:

=REGEX.MID(TRIM(A1),"(?<=,\s).*(?=,\s*" & F1&")")



--ron