FileMaker Pro Custom Functions

/* fnAddressParser™ v3.1 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.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 ( [ 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 ) ) ; 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 ) ; // 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"; 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 ) <> "." ; 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" ; name ; type = "institution" ; name ; type = "address_number" ; number ; type = "number" ; number ; type = "street" ; street ; type = "unit" ; unit ; 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 ) )
 Then copy & paste into FileMaker Advanced's Edit Custom Function window.