#1   Report Post  
Old December 9th 05, 09:24 PM posted to microsoft.public.excel.worksheet.functions
BorisS
 
Posts: n/a
Default 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

  #2   Report Post  
Old December 9th 05, 09:48 PM posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default mid and find

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



  #3   Report Post  
Old December 9th 05, 10:11 PM posted to microsoft.public.excel.worksheet.functions
BorisS
 
Posts: n/a
Default 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.

Thanks already.
--
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




  #4   Report Post  
Old December 9th 05, 10:30 PM posted to microsoft.public.excel.worksheet.functions
BorisS
 
Posts: n/a
Default 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):

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




  #5   Report Post  
Old December 10th 05, 03:13 PM posted to microsoft.public.excel.worksheet.functions
Domenic
 
Posts: n/a
Default 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(D110,MATCH(TRUE,INDEX(ISNUMBER(SEARCH( D110,A1)),0),0)),"")

....where D110 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



  #6   Report Post  
Old December 10th 05, 06:08 PM posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld
 
Posts: n/a
Default 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.

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 01:59 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017