Parsing the directional out of addresses using nested IF .

  • Thread starter Thread starter Dan
  • Start date Start date
D

Dan

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
 
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.
Click to expand...

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.
 
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?
Click to expand...

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
 
Back
Top