AnsweredAssumed Answered

Reorganize a Parsed Street Addres into a Sequentially Organized List

Question asked by RWR1911 on Mar 9, 2013
Latest reply on Mar 10, 2013 by JimMac

Title

Reorganize a Parsed Street Addres into a Sequentially Organized List

Post

     My db includes an imported Address field comprised of 3 parts: St #, St Name and St Unit, i.e., 9 Washington Street #A7 (note there is no space between # and A7).

      Regarding the parsing of 9 Washington Street #A7:

     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 ) ];  If ( cpos ; Trim ( Right ( Address ; L - cpos ) ) ) ) extracts A7 as the St Unit. (If there is no St Unit, the extraction leaves St Unit blank as it should be.)    

     These calculations parse perfectly and do everything the parse is designed to do.

     The problem is in trying to reorganize the 3 parts back into a sequentially organized 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 what I wish to occur:

     2 Adams Road 3B

     2 Adams Road 6

     2 Adams Road 7X

     2 Adams Road 8

     2 Adams Road 10

     2 Adams Road 10X

     2 Adams Road 27B

     2 Adams Road A

     2 Adams Road A3

     2 Adams Road A7

     2 Adams Road D

     2 Adams Road D4

     2 Adams Road DD

Instead the addresses order as follows:

2 Adams Road 10

2 Adams Road 10X

2 Adams Road 27B

2 Adams Road 3B

2 Adams Road 6

2 Adams Road 7X

2 Adams Road 8

2 Adams Road A

2 Adams Road A3

2 Adams Road A7

2 Adams Road D

2 Adams Road D4

2 Adams Road DD

                     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 a sort ascending order of St Name first, St # second, and St Unit third.   

                     The St # sorts sequentially: 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 out of order as noted in the italicized, underlined lists above.

                     What modification must be made to the third calculation field to permit St Unit to sequentially order itself just as the St # and St Name already do? (Again: St # is Number, St Name is Text, and St Unit is Text.)

Outcomes