3 Replies Latest reply on Mar 11, 2013 8:13 PM by philmodjunk

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

    RWR1911

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

        • 1. Re: Parse a Street Address into 3 Separate Parts: St #, St Name, St Unit
          philmodjunk

               Sure you posted this in the right section? Seems like it should be posted in the FM Pro Forum section.

               You can modify the last calculation to check for the existance of the # character and return null if it is not present:

               Let ( [ L = Length ( Address ) ; cpos = Position ( Address ; "#" ; L ; -1 ) ];
                         If ( cpos ; Trim ( Right ( Address ; L - cpos ) ) ) )

          • 2. Re: Parse a Street Address into 3 Separate Parts: St #, St Name, St Unit
            RWR1911

                           Thank you! The modification that PhilModJunk suggested for the third calculation worked. Each of the three parts parse as needed.

                 My next goal is to reassemble the three parts into a sequential list where the addresses order as follows:

                 9 Washington Street

                 9 Washington Street 1

                 9 Washington Street 2

                 9 Washington Street 2B

                 9 Washington Street 12

                 9 Washington Street 12B

                 so on and so forth.

                  

            Instead the addresses order as follows:
                          

            9 Washington Street

            9 Washington Street 1

            9 Washington Street 12

            9 Washington Street 12B

            9 Washington Street 2

            9 Washington Street 2B

                 Another example of the sequential ordering desired is:

                 2 Adams Road 6

                 2 Adams Road 10

                 2 Adams Road 3B

                 2 Adams Road 7X

                 2 Adams Road 10X

                 2 Adams Road A7

            Instead the addresses order as follows:

            2 Adams Road 10

            2 Adams Road 10X

            2 Adams Road 3B

            2 Adams Road 6

            2 Adams Road 7X

            2 Adams Road A7

                  

                 To sequentially order the addresses the way I wanted I created a Sort Script (named: Sort by St Name, St #, St Unit) in a list layout in the order of St Name, St #, and St Unit. Each was sorted in ascending order.  

                 The St # sorts sequentially as desired: 1, 2, 3, 4, 5, 6, 6.5, 7, 8, 9, 10, 11, etc. The St Name sorts alphabetically A to Z. The St Unit sorts as noted in the italicized lists above.

                 What modification must be made to the third calculation field to permit St Unit to sequentially order itself just as the St # already does?

            • 3. Re: Parse a Street Address into 3 Separate Parts: St #, St Name, St Unit
              philmodjunk

                   Your last field has both text and numeric data. You need two fields, one with the numeric data ( 1, 2 , 12...) one with the text: (A, B, ...)

                   Then you can sort first by the numeric portion, then by the text portion.