AnsweredAssumed Answered

Parse a Street Address into 3 Separate Parts: St #, St Name, St Unit

Question asked by RWR1911 on Mar 8, 2013
Latest reply on Mar 11, 2013 by philmodjunk

Title

Parse a Street Address into 3 Separate Parts: St #, St Name, St Unit

Post

     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 #?

Outcomes