On Thu, 23 Oct 2003 08:44:29 -0700, "Dan"
wrote:

I have addresses that have intermittant directional
charactors i.e. N street or S street or NW street. I used
this combination of functions to conditionally parse the
information but my logic is flawed somewhere. Any help
would be appreciated. The fuctions I used were IF() Left()
Find()

=IF("S"=LEFT(E21,FIND("S ",E21,1)),"S",IF("N"=LEFT(E21,FIND
("N ",E21,1)),"N"," "))

Cell E21 has the following address N Avenue 235.
The result of the function is #VALUE!

The preceeding cell has S Avenue 67 and the return value
was S.

The first part of the formula seems to work but the nested
if statments do not.

Your problem is that the FIND function returns a #VALUE! error if find_text
does not appear within search_text. Since search_text does not have an "S ",
that part of your IF statement returns a #VALUE! error. The IF will only
continue if the result of the logical_test is TRUE or FALSE. It will not
continue if the result of an operation results in an error.

You could do something like:

=IF(ISERR(FIND("S ",LEFT(E21,2))),IF(ISERR(FIND("N
",LEFT(E21,2))),IF(ISERR(FIND("NW ",LEFT(E21,3))),"no directional"),"N"),"S")

Although it becomes cumbersome to extend it. And since there is a limit of 7
function nestings, it may not be possible to handle all possible directionals.

So a different approach might be more flexible. If I understand your method, a
directional is characterized by a compass direction that is capitalized and
followed by a <space. This formula might be helpful:

=CHOOSE(MATCH(FALSE,ISERR(FIND({"N";"NE";"E";"SE"; "S";"SW";"W";"NW";""}
&" ",E21)),0),"N","NE","E","SE","S","SW","W","NW" ,"")

will return the directional if it is found, and a null string if it is not
found. If there is no entry in the address line, the formula returns #N/A!


--ron