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
.