fnAddressParser ( address ; type )

This CF parses address parts, allowing developers to create calculation fields for single address fields for display and sorting.

Average rating: 4.4 (39 votes) Log in to vote

Drew Wesley - Show more from this author
Drewsol
https://www.drewsol.com

Sample input:

fnAddressParser ( "123 W. 3rd St. #12, Los Angeles, CA 90040" , "Street" )

fnAddressParser ( "123 W. 3rd St. #12, Los Angeles, CA 90040" , "StreetSort" )

fnAddressParser ( "
Drew Wesley
Drewsol
123 W. 3rd St. #12
Los Angeles, CA 90040
" , "City" )

Sample output:

W. 3rd St.

003 St., #21

Los Angeles

Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

/*
fnAddressParser™ v3.3
Copyright © 2009 Drewsol

GNU Lesser General Public License:  This function is free software: you can redistribute it and/or modify it under the terms of the GNU Lesser General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.  This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU Lesser General Public License for more details.  You should have received a copy of the GNU Lesser General Public License along with this program.  If not, see <http://www.gnu.org/licenses/>.

This CF parses address parts, allowing developers to create calculation fields for single address fields for display and sorting.

fnAddressParser™ ( address ; type )
 - address
      Any address or address field
 - type
      name, institution, address_number, number, street, unit, street_address, city, state, zip, csz, streetname_sort, streetsort

Disclaimer:
   (1) This function is a parsing tool that uses an applied systematic methodology to linearly parse data from a larger string.
   (2) It is largely dependent on modern lists (of street type designations and unit types), some of which may be missing in this function.
   (3) This functon is an ongoing development assisted by moderate testing and user feedback.

ChangeLog:
   v3.3 - Unit Type.
   v3.2 - Allowance for Circle. Fixes to remove extra carriage returns.
   v3.1 - Allowance for Court.
   v3.0 - Public release.
   v2.4 - Support for PO Boxes.  Changed name from fnStreetSmart™ to fnAddressParser™.
   v2.3 - Accepts a name and/or company above the address.
   v2.2 - Corrected parsing issue for directional prefixes followed by numeric street names.
   v2.1 - Improvements in parsing address lines and parts.
   v2.0 - Consolidates three previous functions, fnStreetSmartL1, fnStreetSmartL2, fnStreetSort as an initial release.
*/


Let ( [

   street_type_array =
// Not currently being used (no foreach loops in FileMaker calculations).
"Avenue		|Ave." & "¶" &
"Street		|St." & "¶" &
"Boulevard	|Blvd." & "¶" &
"Road		|Rd." & "¶" &
"Lane		|Ln." & "¶" &
"Way		|Wy." & "¶" &
"Drive		|Dr." & "¶" &
"Court		|Ct." & "¶" &
"Circle		|Cir."
;

   pos_line1_beg =
   1 ;
   pos_line1_end =
   Position ( address & ¶ ; "¶" ; 1 ; 1 ) ;
   pos_line2_beg = 
   Case ( ValueCount ( address ) > 1 ; pos_line1_end + 1 ) ;
   pos_line2_end =
   Case ( ValueCount ( address ) > 1 ; Position ( address & ¶  ; "¶" ; 1 ; 2 ) ) ;
   pos_line3_beg =
   Case ( ValueCount ( address ) > 2 ; pos_line2_end + 1 ) ;
   pos_line3_end =
   Case ( ValueCount ( address ) > 2 ; Position ( address & ¶  ; "¶" ; 1 ; 3 ) ) ;
   pos_line4_beg =
   Case ( ValueCount ( address ) > 3 ; pos_line3_end + 1 ) ;
   pos_line4_end =
   Case ( ValueCount ( address ) > 3 ; Position ( address & ¶  ; "¶" ; 1 ; 4 ) ) ;

   // Processing needed if address is on single line.

   pos1 =
   Case (
      //   Seek a Street Type
      //   http://en.wikipedia.org/wiki/Street_name#Street_type_designations

      // Avenue
      PatternCount ( address ; " Ave. " ) > 0 ; Position ( address ; " Ave. " ; 1 ; 1 ) ;
      PatternCount ( address ; " Ave " ) > 0 ; Position ( address ; " Ave " ; 1 ; 1 ) ;
      PatternCount ( address ; " Avenue " ) > 0 ; Position ( address ; " Avenue " ; 1 ; 1 ) ;
      //
      PatternCount ( address ; " Ave." ) > 0 ; Position ( address ; " Ave." ; 1 ; 1 ) ;
      PatternCount ( address ; " Ave" ) > 0 ; Position ( address ; " Ave" ; 1 ; 1 ) ;
      PatternCount ( address ; " Avenue" ) > 0 ; Position ( address ; " Avenue" ; 1 ; 1 ) ;

      // Street
      PatternCount ( address ; " St. " ) > 0 ; Position ( address ; " St. " ; 1 ; 1 ) ;
      PatternCount ( address ; " St " ) > 0 ; Position ( address ; " St " ; 1 ; 1 ) ;
      PatternCount ( address ; " Street " ) > 0 ; Position ( address ; " Street " ; 1 ; 1 ) ;
      //
      PatternCount ( address ; " St." ) > 0 ; Position ( address ; " St." ; 1 ; 1 ) ;
      PatternCount ( address ; " St" ) > 0 ; Position ( address ; " St" ; 1 ; 1 ) ;
      PatternCount ( address ; " Street" ) > 0 ; Position ( address ; " Street" ; 1 ; 1 ) ;

      // Boulevard
      PatternCount ( address ; " Blvd. " ) > 0 ; Position ( address ; " Blvd. " ; 1 ; 1 ) ;
      PatternCount ( address ; " Blvd " ) > 0 ; Position ( address ; " Blvd " ; 1 ; 1 ) ;
      PatternCount ( address ; " Boulevard " ) > 0 ; Position ( address ; " Boulevard " ; 1 ; 1 ) ;
      //
      PatternCount ( address ; " Blvd." ) > 0 ; Position ( address ; " Blvd." ; 1 ; 1 ) ;
      PatternCount ( address ; " Blvd" ) > 0 ; Position ( address ; " Blvd" ; 1 ; 1 ) ;
      PatternCount ( address ; " Boulevard" ) > 0 ; Position ( address ; " Boulevard" ; 1 ; 1 ) ;

      // Road
      PatternCount ( address ; " Rd. " ) > 0 ; Position ( address ; " Rd. " ; 1 ; 1 ) ;
      PatternCount ( address ; " Rd " ) > 0 ; Position ( address ; " Rd " ; 1 ; 1 ) ;
      PatternCount ( address ; " Road " ) > 0 ; Position ( address ; " Road " ; 1 ; 1 ) ;
      //
      PatternCount ( address ; " Rd." ) > 0 ; Position ( address ; " Rd." ; 1 ; 1 ) ;
      PatternCount ( address ; " Rd" ) > 0 ; Position ( address ; " Rd" ; 1 ; 1 ) ;
      PatternCount ( address ; " Road" ) > 0 ; Position ( address ; " Road" ; 1 ; 1 ) ;

      // Lane
      PatternCount ( address ; " Ln. " ) > 0 ; Position ( address ; " Ln. " ; 1 ; 1 ) ;
      PatternCount ( address ; " Ln " ) > 0 ; Position ( address ; " Ln " ; 1 ; 1 ) ;
      PatternCount ( address ; " Lane " ) > 0 ; Position ( address ; " Lane " ; 1 ; 1 ) ;
      //
      PatternCount ( address ; " Ln." ) > 0 ; Position ( address ; " Ln." ; 1 ; 1 ) ;
      PatternCount ( address ; " Ln" ) > 0 ; Position ( address ; " Ln" ; 1 ; 1 ) ;
      PatternCount ( address ; " Lane" ) > 0 ; Position ( address ; " Lane" ; 1 ; 1 ) ;

      // Way
      PatternCount ( address ; " Wy. " ) > 0 ; Position ( address ; " Wy. " ; 1 ; 1 ) ;
      PatternCount ( address ; " Wy " ) > 0 ; Position ( address ; " Wy " ; 1 ; 1 ) ;
      PatternCount ( address ; " Way " ) > 0 ; Position ( address ; " Way " ; 1 ; 1 ) ;
      //
      PatternCount ( address ; " Wy." ) > 0 ; Position ( address ; " Wy." ; 1 ; 1 ) ;
      PatternCount ( address ; " Wy" ) > 0 ; Position ( address ; " Wy" ; 1 ; 1 ) ;
      PatternCount ( address ; " Way" ) > 0 ; Position ( address ; " Way" ; 1 ; 1 ) ;

      // Drive
      PatternCount ( address ; " Dr. " ) > 0 ; Position ( address ; " Dr. " ; 1 ; 1 ) ;
      PatternCount ( address ; " Dr " ) > 0 ; Position ( address ; " Dr " ; 1 ; 1 ) ;
      PatternCount ( address ; " Drive " ) > 0 ; Position ( address ; " Drive " ; 1 ; 1 ) ;
      //
      PatternCount ( address ; " Dr." ) > 0 ; Position ( address ; " Dr." ; 1 ; 1 ) ;
      PatternCount ( address ; " Dr" ) > 0 ; Position ( address ; " Dr" ; 1 ; 1 ) ;
      PatternCount ( address ; " Drive" ) > 0 ; Position ( address ; " Drive" ; 1 ; 1 ) ;

      // Court
      PatternCount ( address ; " Ct. " ) > 0 ; Position ( address ; " Ct. " ; 1 ; 1 ) ;
      PatternCount ( address ; " Ct " ) > 0 ; Position ( address ; " Ct " ; 1 ; 1 ) ;
      PatternCount ( address ; " Court " ) > 0 ; Position ( address ; " Court " ; 1 ; 1 ) ;
      //
      PatternCount ( address ; " Ct." ) > 0 ; Position ( address ; " Ct." ; 1 ; 1 ) ;
      PatternCount ( address ; " Ct" ) > 0 ; Position ( address ; " Ct" ; 1 ; 1 ) ;
      PatternCount ( address ; " Court" ) > 0 ; Position ( address ; " Court" ; 1 ; 1 ) ;

      // Circle
      PatternCount ( address ; " Cir. " ) > 0 ; Position ( address ; " Cir. " ; 1 ; 1 ) ;
      PatternCount ( address ; " Cir " ) > 0 ; Position ( address ; " Cir " ; 1 ; 1 ) ;
      PatternCount ( address ; " Circle " ) > 0 ; Position ( address ; " Circle " ; 1 ; 1 ) ;
      //
      PatternCount ( address ; " Cir." ) > 0 ; Position ( address ; " Cir." ; 1 ; 1 ) ;
      PatternCount ( address ; " Cir" ) > 0 ; Position ( address ; " Cir" ; 1 ; 1 ) ;
      PatternCount ( address ; " Circle" ) > 0 ; Position ( address ; " Circle" ; 1 ; 1 )
   ) ;

   pos2 =
   Case (
      // Seek a PO Box
      PatternCount ( address ; "PO Box" ) > 0 ; Position ( address ; "PO Box" ; 1 ; 1 ) ;
      PatternCount ( address ; "P.O. Box" ) > 0 ; Position ( address ; "P.O. Box" ; 1 ; 1 ) ;
      PatternCount ( address ; "P. O. Box" ) > 0 ; Position ( address ; "P. O. Box" ; 1 ; 1 ) ;
      PatternCount ( address ; "P O Box" ) > 0 ; Position ( address ; "P O Box" ; 1 ; 1 )
   ) ;

   pos3 =
   Case (
      //   Seek a Unit Type
      PatternCount ( address ; " Apt." ) > 0 ; Position ( address ; " Apt." ; 1 ; 1 ) ;
      PatternCount ( address ; " Apt" ) > 0 ; Position ( address ; " Apt" ; 1 ; 1 ) ;
      PatternCount ( address ; " Apartment" ) > 0 ; Position ( address ; " Apartment" ; 1 ; 1 ) ;
      PatternCount ( address ; " Unit" ) > 0 ; Position ( address ; " Unit" ; 1 ; 1 ) ;
      PatternCount ( address ; " Ste." ) > 0 ; Position ( address ; " Ste." ; 1 ; 1 ) ;
      PatternCount ( address ; " Ste" ) > 0 ; Position ( address ; " Ste" ; 1 ; 1 ) ;
      PatternCount ( address ; " Suite" ) > 0 ; Position ( address ; " Suite" ; 1 ; 1 ) ;
      PatternCount ( address ; " Bld." ) > 0 ; Position ( address ; " Bld." ; 1 ; 1 ) ;
      PatternCount ( address ; " Bld" ) > 0 ; Position ( address ; " Bld" ; 1 ; 1 ) ;
      PatternCount ( address ; " Bldg." ) > 0 ; Position ( address ; " Bldg." ; 1 ; 1 ) ;
      PatternCount ( address ; " Bldg" ) > 0 ; Position ( address ; " Bldg" ; 1 ; 1 ) ;
      PatternCount ( address ; " Building" ) > 0 ; Position ( address ; " Building" ; 1 ; 1 ) ;
      PatternCount ( address ; " No." ) > 0 ; Position ( address ; " No." ; 1 ; 1 ) ;
      PatternCount ( address ; " No" ) > 0 ; Position ( address ; " No" ; 1 ; 1 ) ;
      PatternCount ( address ; " Number" ) > 0 ; Position ( address ; " Number" ; 1 ; 1 ) ;
      PatternCount ( address ; " #" ) > 0 ; Position ( address ; " #" ; 1 ; 1 ) ;
      PatternCount ( address ; "#" ) > 0 ; Position ( address ; " #" ; 1 ; 1 )
   ) ;

   unit_length =
   Case (
      //   Seek a Unit Type
      PatternCount ( address ; " Apt." ) > 0 ; 4 ;
      PatternCount ( address ; " Apt" ) > 0 ; 3 ;
      PatternCount ( address ; " Apartment" ) > 0 ; 9 ;
      PatternCount ( address ; " Unit" ) > 0 ; 4 ;
      PatternCount ( address ; " Ste." ) > 0 ; 4 ;
      PatternCount ( address ; " Ste" ) > 0 ; 3 ;
      PatternCount ( address ; " Suite" ) > 0 ; 5 ;
      PatternCount ( address ; " Bld." ) > 0 ; 4 ;
      PatternCount ( address ; " Bld" ) > 0 ; 3 ;
      PatternCount ( address ; " Bldg." ) > 0 ; 5 ;
      PatternCount ( address ; " Bldg" ) > 0 ; 4 ;
      PatternCount ( address ; " Building" ) > 0 ; 8 ;
      PatternCount ( address ; " No." ) > 0 ; 3 ;
      PatternCount ( address ; " No" ) > 0 ; 2 ;
      PatternCount ( address ; " Number" ) > 0 ; 6 ;
      PatternCount ( address ; " #" ) > 0 ; 1 ;
      PatternCount ( address ; "#" ) > 0 ; 1
   ) ;

   unit_type =
   Case (
      //   Seek a Unit Type
      PatternCount ( address ; " Apt." ) > 0 ; "Apt." ;
      PatternCount ( address ; " Apt" ) > 0 ; "Apt" ;
      PatternCount ( address ; " Apartment" ) > 0 ; "Apartment" ;
      PatternCount ( address ; " Unit" ) > 0 ; "Unit" ;
      PatternCount ( address ; " Ste." ) > 0 ; "Ste." ;
      PatternCount ( address ; " Ste" ) > 0 ; "Ste" ;
      PatternCount ( address ; " Suite" ) > 0 ; "Suite" ;
      PatternCount ( address ; " Bld." ) > 0 ; "Bld." ;
      PatternCount ( address ; " Bld" ) > 0 ; "Bld" ;
      PatternCount ( address ; " Bldg." ) > 0 ; "Bldg." ;
      PatternCount ( address ; " Bldg" ) > 0 ; "Bldg" ;
      PatternCount ( address ; " Building" ) > 0 ; "Building" ;
      PatternCount ( address ; " No." ) > 0 ; "No." ;
      PatternCount ( address ; " No" ) > 0 ; "No" ;
      PatternCount ( address ; " Number" ) > 0 ; "Number" ;
      PatternCount ( address ; " #" ) > 0 ; "#" ;
      PatternCount ( address ; "#" ) > 0 ; "#"
   ) ;

   //   DETECT LINE 1, 2, OR BOTH

   address_start_line = 
   Case (
      Max ( pos1 ; pos2 ; 1 ) ≥ pos_line1_beg and Max ( pos1 ; pos2 ; 1 ) ≤ pos_line1_end ; 1 ;
      Max ( pos1 ; pos2 ; 1 ) ≥ pos_line2_beg and Max ( pos1 ; pos2 ; 1 ) ≤ pos_line2_end ; 2 ;
      Max ( pos1 ; pos2 ; 1 ) ≥ pos_line3_beg and Max ( pos1 ; pos2 ; 1 ) ≤ pos_line3_end ; 3 ;
      Max ( pos1 ; pos2 ; 1 ) ≥ pos_line4_beg and Max ( pos1 ; pos2 ; 1 ) ≤ pos_line4_end ; 4
   ) ;

   address_start_pos =
   Case (
      address_start_line = 1 ; pos_line1_beg ;
      address_start_line = 2 ; pos_line2_beg ;
      address_start_line = 3 ; pos_line3_beg ;
      address_start_line = 4 ; pos_line4_beg
   ) ;      

   name =
   Case (
      address_start_line > 1 ;
      MiddleValues ( address ; 1 ; address_start_line - 1 )
   ) ;

   address =
   MiddleValues ( address ; address_start_line ; 100 ) ;

   pos1a =
      pos1 - Length ( name ) ;

   pos2a =
      pos2 - Length ( name ) ;

   pos3a =
      pos3 - Length ( name ) ;

   wc_line1 = 
   Case (

      // No unit.
      pos3 <> "" ;
      WordCount ( Middle ( address ; 1 ; pos3a ) ) +
      1 + 
      If ( Middle ( address ; pos3a + 1 ; 2 ) = "# " ; - 1 ) +
      Case ( Middle ( address ; pos3a + 1 + unit_length ; 1 ) = " " ; 1 ) ;

      // PO Box
      pos2 <> "" ;
      Let ( [
         pos2boxend = If ( pos2 <> "" ; Position ( address ; "Box" ; 1 ; 1 ) + 3 ) ;
         wc_line2a = pos2boxend - pos2a
         ] ;
         WordCount ( Middle ( address ; pos2a ; wc_line2a ) ) + 1
      ) ;

      // Only CSZ
      pos1 = "" and pos2 = "" and pos3 = "" and ValueCount ( address ) = 1 ;
      0 ;

      // Else
      WordCount ( Middle ( address ; 1 ; pos1a ) ) + 1
   ) ;

   wc_line2 = 
   Case (

      // Only CSZ
      pos1 = "" and pos2 = "" and pos3 = "" and ValueCount ( address ) = 1 ;
      WordCount ( address ) ;

      // Else
      WordCount ( address ) - wc_line1
   ) ;

   line1 = 
   Case (
      ValueCount ( address ) = 1 ;
      MiddleWords ( address ; 1 ; wc_line1 ) ;
      ValueCount ( address ) = 2 ;
      GetValue ( address ; 1 ) ;
      ValueCount ( address ) = 3 ;
      GetValue ( address ; 1 ) & " " & GetValue ( address ; 2 ) ;
      address
   ) ;

   line2 = 
   Case (
      ValueCount ( address ) = 1 ;
      MiddleWords ( address ; wc_line1 + 1 ; wc_line2 ) ;
      ValueCount ( address ) = 2 ;
      GetValue ( address ; 2 ) ;
      ValueCount ( address ) = 3 ;
      GetValue ( address ; 3 ) ;
      address
   ) ;


   //   STREETSORT

   field = line1 ;

   alphastring = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz. " ;

   directional_prefix_v1 =
   Case (
      Length ( MiddleWords ( field ; 1 ; 1 ) ) = Length ( Filter ( MiddleWords ( field ; 1 ; 1 ) ; "1234567890-" ) ) ;
      MiddleWords ( field ; 2 ; 1 ) ;
      MiddleWords ( field ; 1 ; 1 )
   ) ;

   directional_prefix_exists =
   Case (
      directional_prefix_v1 = "N" or
      directional_prefix_v1 = "No" or
      directional_prefix_v1 = "North" or
      directional_prefix_v1 = "E" or
      directional_prefix_v1 = "Ea" or
      directional_prefix_v1 = "East" or
      directional_prefix_v1 = "S" or
      directional_prefix_v1 = "So" or
      directional_prefix_v1 = "South" or
      directional_prefix_v1 = "W" or
      directional_prefix_v1 = "West" ;
      1 ;
      0
   ) ;

   directional_prefix =
   Case (
      directional_prefix_exists = 1 and Length ( directional_prefix_v1 ) = 1 ;
      directional_prefix_v1 & "." ;
      directional_prefix_exists = 0 ;
      "" ;
     directional_prefix_v1
   ) ;

   streetname_sort_v1 =
   Case (
      //   If first word has only numbers and second word is N. E. S. W.
      Filter ( LeftWords ( field ; 1 ) ; alphastring ) = "" and 
      directional_prefix_exists = 1 ;
      //   Then
      MiddleWords ( field ; 3 ; WordCount ( field ) - 2 ) ;

      //If first word has letters and is N. E. S. W.
      Filter ( LeftWords ( field ; 1 ) ; alphastring ) > "" and 
      directional_prefix_exists = 1 ;
      //   Then
      MiddleWords ( field ; 2 ; WordCount ( field ) - 1 ) ;

      //   If first word has only numbers and second word is not N. E. S. W.
      Filter ( LeftWords ( field ; 1 ) ; alphastring ) = "" and 
      directional_prefix_exists = 0 ;
      //   Then
      MiddleWords ( field ; 2 ; WordCount ( field ) - 1 ) ;

      field
   ) ;

   streetname_sort =
   Let ( [
      streetname_number = GetAsNumber ( LeftWords ( streetname_sort_v1 ; 1 ) ) ;
      streetname = MiddleWords ( streetname_sort_v1 ; 2 ; WordCount ( streetname_sort_v1 ) - 1 )
      ] ;
      Case (
         Length ( streetname_number ) = 1 ;
         "00" & streetname_number & " " & streetname ;
         Length ( streetname_number ) = 2 ;
         "0" & streetname_number & " " & streetname ;
         Length ( streetname_number ) = 3 ;
         "" & streetname_number & " " & streetname ;
         streetname_sort_v1
      )
   ) ;


   //   STREETSMART LINE 1 - NUMBER, STREET, UNIT

   alphanumbericstring = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz. 1234567890" ;

   //Number
   number_v1 = 
   Case (
      Filter ( Left ( MiddleWords ( line1 ; 2 ; 1 ) ; 1 ) ; "1234567890" ) <> "" ;
      Left ( Filter ( line1 ; alphastring ) ; 1 ) ;
      Left ( Filter ( line1 ; alphastring ) ; 1 ) 
   ) ;
   pos_number = Position ( line1 ; number_v1 ; 1 ; 1 ) ;
   number_v2 = Left ( line1 ; pos_number - 1 ) ;
   number_v3 = Trim ( number_v2 ) ;
   number = number_v3 ;

   //   Street
   street_v1 = Middle ( line1 ; pos_number ; 100 ) ;
   street_v2 =
   Case (
      pos2 <> "" ;
      line1 ;
      directional_prefix_exists = 1 ;
      Trim ( Filter ( MiddleWords ( street_v1 ; 1 ; 2 ) ; alphanumbericstring ) ) & " " & Trim ( Filter ( MiddleWords ( street_v1 ; 3 ; 100 ) ; alphastring ) ) ;
      Trim ( Filter ( MiddleWords ( street_v1 ; 1 ; 1 ) ; alphanumbericstring ) ) & " " & Trim ( Filter ( MiddleWords ( street_v1 ; 2 ; 100 ) ; alphastring ) )
   ) ;
   street_v3 =
   Case (
      RightWords ( street_v2 ; 1 ) = "Apt" or
      RightWords ( street_v2 ; 1 ) = "Apartment" or
      RightWords ( street_v2 ; 1 ) = "Unit" or
      RightWords ( street_v2 ; 1 ) = "Ste" or
      RightWords ( street_v2 ; 1 ) = "Suite" or
      RightWords ( street_v2 ; 1 ) = "Bld" or
      RightWords ( street_v2 ; 1 ) = "Bldg" or
      RightWords ( street_v2 ; 1 ) = "Building" or
      RightWords ( street_v2 ; 1 ) = "No" or
      RightWords ( street_v2 ; 1 ) = "Number" or
      RightWords ( street_v2 ; 1 ) = "#";
      Trim ( LeftWords ( street_v2 ; WordCount ( street_v2 ) - 1 ) ) ;
      Trim ( street_v2 )
   ) ;
   street_v4 =
   Case (
      RightWords ( street_v3 ; 1 ) = "Ave" and Right ( street_v3 ; 1 ) <> "." or
      RightWords ( street_v3 ; 1 ) = "St"and Right ( street_v3 ; 1 ) <> "." or
      RightWords ( street_v3 ; 1 ) = "Blvd" and Right ( street_v3 ; 1 ) <> "." or
      RightWords ( street_v3 ; 1 ) = "Rd" and Right ( street_v3 ; 1 ) <> "." or
      RightWords ( street_v3 ; 1 ) = "Ln" and Right ( street_v3 ; 1 ) <> "." or
      RightWords ( street_v3 ; 1 ) = "Wy" and Right ( street_v3 ; 1 ) <> "." or
      RightWords ( street_v3 ; 1 ) = "Dr" and Right ( street_v3 ; 1 ) <> "." or
      RightWords ( street_v3 ; 1 ) = "Ct" and Right ( street_v3 ; 1 ) <> "." or
      RightWords ( street_v3 ; 1 ) = "Cir" and Right ( street_v3 ; 1 ) <> "." ;
      street_v3 & "." ;
      street_v3
   ) ;
   //street = Case ( pos2 = "" ; street_v4 ; line1 ) ;
   street = street_v4 ;

   //   Unit
   pos_street = Position ( line1 ; street_v3 ; 0 ; 1 ) + Length ( street_v3 ) ;

   unit_v1 = Middle ( line1 ; pos_street ; 100 ) ;
   unit_v2 =
   Case (
      Left ( unit_v1 ; 1 ) = "." ; Middle ( unit_v1 ; 2 ; 100 ) ; unit_v1
   ) ;
   unit_v3 = Trim ( unit_v2 ) ;
   unit_v4 = Filter ( unit_v3 ; "#0123456789-" ) ;
   pos_unit = Position ( unit_v3 ; Left ( unit_v4 ; 1 ) ; 1 ; 1 ) ;
   unit_v5 = Middle ( unit_v3 ; pos_unit ; 100 ) ;
   unit = unit_v5 ;


   //   STREETSMART LINE 2 - CITY, STATE, ZIP

   //   Current List of CA States - 2006
   state_list =
"AL¶AK¶AZ¶AR¶CA¶CO¶CT¶DE¶FL¶GA¶HI¶ID¶IL¶IN¶IA¶KS¶KY¶LA¶ME¶MD¶MA¶MI¶MN¶MS¶MO¶MT¶NE¶NV¶NH¶NJ¶NM¶NY¶NC¶ND¶OH¶OK¶OR¶PA¶RI¶SC¶SD¶TN¶TX¶UT¶VT¶VA¶WA¶WV¶WI¶WY¶AS¶DC¶FM¶GU¶MH¶MP¶PW¶PR¶VI" ;

   //   City (text before comma)
   city1 = Left ( line2 ; Position ( line2 ; "," ; 1 ; 1 ) - 1 ) ;
   wc_city1 = WordCount ( city1 ) ;
   //   City (words before comma)
   city2 = LeftWords ( city1 ; wc_city1 ) ;

   //   State (word after comma)
   state1 = MiddleWords ( line2 ; Evaluate ( wc_city1 ) + 1 ; 1 ) ;
   state2 = state1 ;

   //   Separate line2 into values (paragraphs)
   //   Pilcrow is the backwards P paragraph sign
   line2_pilcrow1 = Substitute ( line2 ; " " ; "¶" ) ;
   line2_pilcrow2 = Substitute ( line2_pilcrow1 ; "," ; "¶" ) ;

   //city1/2 and state1/2 depend on comma, city3/4 and state3/4 depend on state list.

   //State (word from state list)
   state3 = FilterValues ( line2_pilcrow2 ; state_list ) ;

   //   Value match with state list
   state_value =
   Case (
      FilterValues ( GetValue ( line2_pilcrow2 ; 1 ) ; state_list ) > 0 ; 1 ;
      FilterValues ( GetValue ( line2_pilcrow2 ; 2 ) ; state_list ) > 0 ; 2 ;
      FilterValues ( GetValue ( line2_pilcrow2 ; 3 ) ; state_list ) > 0 ; 3 ;
      FilterValues ( GetValue ( line2_pilcrow2 ; 4 ) ; state_list ) > 0 ; 4 ;
      FilterValues ( GetValue ( line2_pilcrow2 ; 5 ) ; state_list ) > 0 ; 5 ;
      FilterValues ( GetValue ( line2_pilcrow2 ; 6 ) ; state_list ) > 0 ; 6 ;
      FilterValues ( GetValue ( line2_pilcrow2 ; 7 ) ; state_list ) > 0 ; 7
   ) ;

   vc_state =
   If ( FilterValues ( GetValue ( line2_pilcrow2 ; 1 ) ; state_list ) <> "" ; 1 ; 0 ) +
   If ( FilterValues ( GetValue ( line2_pilcrow2 ; 2 ) ; state_list ) <> "" ; 1 ; 0 ) +
   If ( FilterValues ( GetValue ( line2_pilcrow2 ; 3 ) ; state_list ) <> "" ; 1 ; 0 ) +
   If ( FilterValues ( GetValue ( line2_pilcrow2 ; 4 ) ; state_list ) <> "" ; 1 ; 0 ) +
   If ( FilterValues ( GetValue ( line2_pilcrow2 ; 5 ) ; state_list ) <> "" ; 1 ; 0 ) +
   If ( FilterValues ( GetValue ( line2_pilcrow2 ; 6 ) ; state_list ) <> "" ; 1 ; 0 ) +
   If ( FilterValues ( GetValue ( line2_pilcrow2 ; 7 ) ; state_list ) <> "" ; 1 ; 0 )
   ;

   //   state4 is revised version of $state3 that doesn't have an extra blank value
   state4 = GetValue ( line2_pilcrow2 ; state_value ) ;

   //   state6 grabs the last state value as the true state if there's more than one (state) detected
   state5 = GetValue ( state3 ; vc_state ) ;
   state6 = state5 ;

   //   City (words before state4)
   city3 = Left ( line2 ; Position ( line2 ; state4 & " " ; 1 ; 1 ) - 1 ) ;
   wc_city3 = WordCount ( city3 ) ;
   //   City (words before state4)
   city4 = LeftWords ( city3; wc_city3 ) ;

   //   city5 grabs the city behind the true state if there's more than one (state) detected
   city5 = Left ( line2 ; Position ( line2 ; state6 & " " ; 1 ; 1 ) - 1 ) ;
   wc_city5 = WordCount ( city5 ) ;
   //   City (words before state6)
   city6 = LeftWords ( city5 ; wc_city5 ) ;

   //   True City, State, and Zip

   city =
   Case (
      vc_state = 1 and PatternCount ( line2 ; "," ) = 0 ; city4 ;
      vc_state = 1 and city2 = city4 ; city2 ;
      vc_state > 1 and PatternCount ( line2 ; "," ) > 0 ; city2 ;
      vc_state > 1 and PatternCount ( line2 ; "," ) = 0 ; city6 ;
      city2
   ) ;

   state =
   Case (
      vc_state = 1 and PatternCount ( line2 ; "," ) = 0 ; state4 ;
      vc_state = 1 and state2 = state4 ; state2 ;
      vc_state > 1 and PatternCount ( line2 ; "," ) > 0 ; state2 ;
      vc_state > 1 and PatternCount ( line2 ; "," ) = 0 ; state6 ;
      state2
   ) ;

   zip = Filter ( RightWords ( line2 ; 1 ) ; "0123456789-" )

   ] ;

   Case (
      type = "name" ; Substitute ( name ; ¶ ; "" ) ;
      type = "institution" ; Substitute ( name ; ¶ ; "" ) ;
      type = "address_number" ; number ;
      type = "number" ; number ;
      type = "street" ; street ;
      type = "unit" ; unit ;
      type = "unit_type" ; unit_type ;
      type = "street_address" ; Trim ( number &" " & street & Case ( unit <> "" ; " " & unit ) ) ;
      type = "city" ; city ;
      type = "state" ; state ;
      type = "zip" ; zip ;
      type = "csz" ; city & ", " & state & " " & zip ;
      type = "streetname_sort" ; streetname_sort ;
      type = "streetsort" ; streetname_sort ;

      // Developer Paramaters
      type = "directional_prefix" ; directional_prefix ;
      type = "directional_prefix_v1" ; directional_prefix_v1 ;
      type = "directional_prefix_exists" ; directional_prefix_exists ;
      type = "street_v1" ; street_v1 ;
      type = "street_v2" ; street_v2 ;
      type = "street_v3" ; street_v3 ;
      type = "pos_line1_beg" ; pos_line1_beg ;
      type = "pos_line1_end" ; pos_line1_end ;
      type = "pos_line2_beg" ; pos_line2_beg ;
      type = "pos_line2_end" ; pos_line2_end ;
      type = "pos_line3_beg" ; pos_line3_beg ;
      type = "pos_line3_end" ; pos_line3_end ;
      type = "pos_line4_beg" ; pos_line4_beg ;
      type = "pos_line4_end" ; pos_line4_end ;
      type = "pos1" ; pos1 ;
      type = "pos2" ; pos2 ;
      type = "pos3" ; pos3 ;
      type = "address_start_line" ; address_start_line ;
      type = "address" ; address ;
      type = "wc_line1" ; wc_line1 ;
      type = "wc_line2" ; wc_line2 ;
      type = "line1" ; line1 ;
      type = "line2" ; line2

   )

)

This CF parses address parts, allowing developers to create calculation fields for single address fields for display and sorting.

Comments

		David Graham, San Diego, CA Oct 2, 2009
I don't get the whole point behind the trademark symbol in Drewsol's function names. Is the author claiming that the name of the function uniquely identifies his business? I see nothing unique in the name of this author's custom function names to warrant common law protection. In addition, the product itself is released under open-source license, so it baffles me that the author would seek to protect the name of a product that the author has released ownership rights to. Color me puzzled?!?!? Dave Graham dave at bittailor dot com

		CA, Cincinnati Oct 29, 2009
//http://en.wikipedia.org/wiki/Street_name#Street_type_designations Does this not include 'Court'

		Drew Wesley, Drewsol Oct 30, 2009
Now it does!

		Christian Sauve, Montreal Jul 26, 2013
Great function!

		Rani Taylor, Frederick, MD Sep 20, 2013
Is there a way to include the "Unit type" when parsing out the unit number? i.e. "Apt. 345 "instead of "345". I was hoping to use this to separate Primary address info from secondary.

		Drew Wesley, Drewsol Sep 20, 2013
@Rani - I just updated the function. Use "unit_type" as the parameter. :) @Christian - Thanks!

		Craig Watson, Kailua Kona /Hawaii Jan 22, 2014
Thanks for this function. I am a little lost which type selection to use parse an address so it fits in a standard 6 field address line. For example 731 S. Lewis st Apt 34 I need each item individually 731 S Lewis ST Apt 34 I can get the 731 with "address_Number" I can get the S with "Direction_prefix_v1" I can get the 34 with "Unit" Unable to get street name alone without the direction, the unit type (using("unit_Type") and street type (St.)

		David Simmons, Ideawise Jun 28, 2019
Great function. Here's a more extensive list of street type designations. I transformed them all into the formula version and tried to put them in the calculation, but apparently this puts it over a 30,000 character limit which I didn't even know existed. Probably for the best though. Some might actually cause it to parse in the wrong place. I pulled a few dozen that I thought were useful (Freeway, Highway, Expressway, Pike, etc) and went with that. I'm unclear though on how to pull P.O. Box. It appears to be detecting it, and I keep a separate field for P.O. Box as differentiated from Street Address. But it doesn't appear that it can be parsed as a separate "type". If I'm missing something please let me know. Thanks for the function. Well done.

		David Simmons, Ideawise Jun 28, 2019
Forgot to include the link to the list: https://pe.usps.com/text/pub28/28apc_002.htm

		Zhava Jul 17, 2019
Wonderful function! A small bug - if a street name starts with "St" such as "State Road" or "Stanley Ave." it does not parse the rest of the address correctly. I'm guessing it thinks it is the beginning of "Street," but I don't know how to fix it.

		Sep 17, 2021
How do I use this function? Are there docs? I went to the developers website, but it's just one page. I found an old email but it seems the mailbox is full. Can anyone help?

		Drew Wesley, Drewsol Dec 28, 2021
Hi, I'm just seeing the most recent comments after a long while and will respond to them.

		Martin Pineault, f.i.SCIENCES Développement inc. Nov 22, 2023
Hi Drew, I'm trying to make it Canadien compatible with French support. I ran into a bug, the char '-' (minus) is not supported in the street name. Thank you.

		Martin Pineault, f.i.SCIENCES Développement inc. Nov 22, 2023
I found out that it's because or the function "MiddleWords()".

		Martin Pineault, f.i.SCIENCES Développement inc. Nov 23, 2023
I found out that it's because or the function "MiddleWords()".

		Martin Pineault, f.i.SCIENCES Développement inc. Nov 23, 2023
I found out that it's because or the function "MiddleWords()".

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

fnAddressParser ( address ; type )

Comments

Support this website.

Support this website.