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.

The problem is with the FIND function -- instead of returning FALSE for
the first FIND which looks for "S ", it returns an error, so it never
proceeds on to the next IF.

So you should change your function to check for errors in the FIND:

=IF(NOT(ISERROR(FIND("S ",E21))),"S",IF(NOT(ISERROR(FIND("N
",E21))),"N",""))

Or you could use a user-defined function:

Function Direction(strAddress As String) As String

If InStr(1, strAddress, "S ") 0 Then
Direction = "S"
Exit Function
End If

If InStr(1, strAddress, "N ") 0 Then
Direction = "N"
Exit Function
End If

End Function

--
Dianne

In ,
Dan typed:
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.

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

Ron,

How would you go about parsing the last name out of a full
name string if there are variable information in the full
name string. i.e. some names will have Dr. or MS or two
middle names or a MR and MRS. I attempted a using a right
function but I could not find a function that would count
the characters from the Right to the left untill it found
a space. Does the search fuction have a left to right
switch?
Any Suggestions?

Thank for whatever help you can provide.
-----Original Message-----
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
.

On Thu, 23 Oct 2003 21:07:21 -0700, "dan"
wrote:

How would you go about parsing the last name out of a full
name string if there are variable information in the full
name string. i.e. some names will have Dr. or MS or two
middle names or a MR and MRS. I attempted a using a right
function but I could not find a function that would count
the characters from the Right to the left untill it found
a space. Does the search fuction have a left to right
switch?
Any Suggestions?

Dan,

Doublecheck the syntax on the stuff below, as I'm just giving you some general
notes and don't have enough time to rigorously test this morning.

If there are no suffixes in the string FullName, and the LastName is the last
word in the string, then LastName is the word following the LastSpace.

1. Compute number of spaces in the string:

NumSpace = LEN(FullName) - LEN(SUBSTITUTE(FullName," ",""))

2. Replace the LastSpace with some character (or string) that won't be in any
of the FullNames. I generally use a tilde (~).

FullNameWithTilde = SUBSTITUTE(FullName," ","~",NumSpace)

3. Get the location of the tilde within FullName:

LocTilde = FIND("~",FullNameWithTilde)

4. Use either a MID or RIGHT function to get the last word:

=MID(FullName,LocTilde+1,255)

or
=RIGHT(FullName,LEN(FullName)-LocTilde)

Obviously, all of the above can be nested, but I won't do it here because,
without debugging, I'd be certain to screw up the parentheses <g.

If the name might have suffixes, after you've parsed out the last word, you'd
need to check it against a list of possible suffixes, and proceed accordingly.

HTH,

--ron