Remember Me? December 9th 05, 09:24 PM posted to microsoft.public.excel.worksheet.functions
 BorisS Posts: n/a mid and find

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 December 9th 05, 09:48 PM posted to microsoft.public.excel.worksheet.functions
 Bob Phillips Posts: n/a mid and find

=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 December 9th 05, 10:11 PM posted to microsoft.public.excel.worksheet.functions
 BorisS Posts: n/a mid and find

looks stellar in creativity, at least. I am wondering...is there a plain
English run-down you can give me? You are using substitute, which is a new
one to me, so it's throwing me off.

--
Boris

"Bob Phillips" wrote:

=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 December 9th 05, 10:30 PM posted to microsoft.public.excel.worksheet.functions
 BorisS Posts: n/a mid and find

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):

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

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

"Bob Phillips" wrote:

=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 December 10th 05, 03:13 PM posted to microsoft.public.excel.worksheet.functions
 Domenic Posts: n/a mid and find

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 10,MATCH(TRUE,INDEX(ISNUMBER(SEARCH( D1 10,A1)),0),0)),"")

....where D1 10 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):

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

Thx again if this is doable. Sorry for the challenge.
--
Boris December 10th 05, 06:08 PM posted to microsoft.public.excel.worksheet.functions
 Ron Rosenfeld Posts: n/a mid and find

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.

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

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

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

All times are GMT +1. The time now is 01:59 AM. Copyright ©2004-2019 ExcelBanter.