Thread: mid and find
View Single Post
  #3   Report Post  
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