Our in-house formula wizard @AnthonyValidity has came up with a helpful formula for a use case we've commonly ran into.
If you are matching addresses but need to ignore a specific floor, unit, apartment, suite, room or building that may be listed after the initial address information, the formula below will take a case insensitive approach to idenfying those by utilizing a Regular Expression comparison type.
Regular Expression: Allows you to build a regular expression (regex) to create custom comparison types for any text field. Knowledge of regular expressions is required to use this comparison type.
RegEx: (?i)^(.+?)(?=\s+(?:Floor(?:\s+\d+)?|Fl(?:\s+\d+)?|#\d*|Unit(?:\s+\d+)?|Suite(?:\s+\d+)?|Ste(?:\s+\d+)?|Apartment(?:\s+\d+)?|Apt(?:\s+\d+)?|Room(?:\s+\d+)?|Rm(?:\s+\d+)?|Building(?:\s+\d+)?|Bldg(?:\s+\d+)?)|$)
Example: 205 N Echo Ave would match to 205 N Echo Ave Unit 13.
Keep in mind our additional address comparison types that can help identify other common nuances with address matching.
Street Address Match: Slightly more rigid criteria than the Relaxed Street Address Match comparison type. This type ignores the differences in street type short forms (such as crescent -> cres, road -> rd, street -> st) and matches all standard abbreviations in the street address to their long forms (e.g., South -> S, Floor -> Fl).
Also, Street Address Match can match records if one record contains a street designator (such as road, rd, drive, dr, street, st, lane, ln) and the other record does not contain a street designator in the street address value.
-
Note: For an address that contains a directional value or a suite number to match, that value needs to be in the address of both records. If one has "North," the other needs at least "N"; if one has a "Suite 100," the other needs at least "Ste 100" or "100".
Relaxed Street Address Match: Parses the street address to the lowest common denominator.
Example:
123 NW Pavillion Ave
123 Pavillion St
123 Pavillion Avenue
123 Pavillion Rd.
Are all seen as "123 Pavillion"
-
If there are no street designators (such as St, Rd, or Ave), then the address will not be parsed into its individual components. In this case, the street address is returned with no changes for matching.
-
Unit numbers, such as suite, apartment, or floor numbers, are not considered the same address. Thus, these are different addresses:
Pugs_Validity
