Parse a Street Address into 3 Separate Parts: St #, St Name, St Unit
My db includes an imported Address field comprised of as many as 3 parts: St #, St Name and St Unit, i.e., 9 Washington Street #A7 (note there is no space between # and A7); however, the vast majority of the imports are comprised of two parts, St # and St Name, i.e., 2 Adams Road, since most properties are single family homes or individually numbered condos.
Regarding the 9 Washington Street #A7 example:
in a field called St # with Calculation result is Number, the calculation, LeftWords ( Address ; 1 ), extracts 9 as the St #;
in a field called St Name with Calculation result is Text, the calculation, If ( Position ( Address ; "#" ; 1 ;1 ) ; MiddleWords ( Address ; 2 ; WordCount ( Address ) - 2 ) ; RightWords ( Address ; WordCount ( Address ) - 1 )), extracts Washington Street as the St Name; and
in a field called St Unit with Calculation result is Text, the calculation, Let ( [ L = Length ( Address ) ; cpos = Position ( Address ; "#" ; L ; -1 ) ]; Trim ( Right ( Address ; L - cpos ) ) ) extracts A7 as the St Unit.
These calculations work perfectly if there is data for the third part, i.e., #A7. If there is only data for two parts, i.e., 2 Adams Road, a problem occurs. The 2 Adams Road extracts as: St # = 2, St Name = Adams Road, and St Unit = 2. In other words, the 2 wraps around and populates the St Unit, but the St Unit field should remain blank, since there is in fact no unit #.
What modification must be made to Let ( [ L = Length ( Address ) ; cpos = Position ( Address ; "#" ; L ; -1 ) ]; Trim ( Right ( Address ; L - cpos ) ) ) so that if there is no third part, the St Unit field remains blank while if there is a third part, the St Unit field populates with the correct unit #?