Separate a Street Address into 3 fields of St #, St Name, and Unit #: Problem with Unit #
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).
St Add: 42 Washington St, C calculates as
St # : 42
St Name: Washington St C
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?