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

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: