AnsweredAssumed Answered

Separate a Street Address into 3 fields of St #, St Name, and Unit #: Problem with Unit #

Question asked by RWR1911 on Feb 18, 2013
Latest reply on Feb 19, 2013 by RWR1911

Title

Separate a Street Address into 3 fields of St #, St Name, and Unit #: Problem with Unit #

Post

     My db includes an imported St Add field comprised of 3 parts: St #, St Name and Unit #, i.e., 9 Washington St, 7. A Unit # can be a #, a single or double letter, or a combination # and letter in order of # first, letter second (i.e., 3A) or letter first, # second (i.e., B9).

     Regarding the 9 Washington St, 7, example:

     in a field called St # with the type as #, the calculation, Left ( St Add ; 4 ), extracts 9 as the St #;  

     in a field called St Name with the type as text, the calculation, Filter ( ( St Add )  ; "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz"  &  " " ), extracts Washington St as the St Name; and  

      

     in a field called Unit # with the type as #, the calculation, Right ( "000" & St Add ; 4 ), extracts 7 as the Unit #.

      

     These calculations work perfectly if there are only numbers on each end of the St Name field as in the example above; however, if the unit # has a single or double letter, or a combination letter and number in any order, the letter separates to the St Name field (which is a text field) and the # separates to the Unit # field (which is a # field).

      

     For example:

     St Add: 42 Washington St, C calculates as

     St # : 42

     St Name: Washington St C

     Unit #:

      

     St Add: 42 Washington St, 3A calculates as

     St # : 42

     St Name: Washington St A

     Unit #: 3

      

     St Add: 42 Washington St, B9 calculates as

     St # : 42

     St Name: Washington St B

     Unit #: 9

      

      

     What modification must be made to the St Name calculation so it will calculate to the end of the street name regardless of how the street name ends, i.e., Alley, Circle, Rd, St, etc, and not pick up any letter in the unit #, i.e, C, A or B?

      

     What modification must be made to the Unit # calculation so it will pick up the actual unit # regardless of whether the unit # is a single or double letter or a combination of a # and a letter?

Outcomes