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

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

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

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.

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:

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

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.