9 Replies Latest reply on Feb 19, 2013 8:00 PM by RWR1911

# Separate a Street Address into 3 fields of St #, St Name, and Unit #: Problem with Unit #

### Title

Separate a Street Address into 3 fields of St #, St Name, and Unit #: Problem with Unit #

### Post

My db includes an imported St Add field comprised of 3 parts: St #, St Name and Unit #, i.e., 9 Washington St, 7. A Unit # can be a #, a single or double letter, or a combination # and letter in order of # first, letter second (i.e., 3A) or letter first, # second (i.e., B9).

Regarding the 9 Washington St, 7, example:

in a field called St # with the type as #, the calculation, Left ( St Add ; 4 ), extracts 9 as the St #;

in a field called St Name with the type as text, the calculation, Filter ( ( St Add )  ; "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz"  &  " " ), extracts Washington St as the St Name; and

in a field called Unit # with the type as #, the calculation, Right ( "000" & St Add ; 4 ), extracts 7 as the Unit #.

These calculations work perfectly if there are only numbers on each end of the St Name field as in the example above; however, if the unit # has a single or double letter, or a combination letter and number in any order, the letter separates to the St Name field (which is a text field) and the # separates to the Unit # field (which is a # field).

For example:

St Add: 42 Washington St, C calculates as

St # : 42

St Name: Washington St C

Unit #:

St Add: 42 Washington St, 3A calculates as

St # : 42

St Name: Washington St A

Unit #: 3

St Add: 42 Washington St, B9 calculates as

St # : 42

St Name: Washington St B

Unit #: 9

What modification must be made to the St Name calculation so it will calculate to the end of the street name regardless of how the street name ends, i.e., Alley, Circle, Rd, St, etc, and not pick up any letter in the unit #, i.e, C, A or B?

What modification must be made to the Unit # calculation so it will pick up the actual unit # regardless of whether the unit # is a single or double letter or a combination of a # and a letter?

• ###### 1. Re: Separate a Street Address into 3 fields of St #, St Name, and Unit #: Problem with Unit #

How consistent is your data?

Is there always a comma after the street name?

If so, we can use that comma with the position function to split off the unit #.

• ###### 2. Re: Separate a Street Address into 3 fields of St #, St Name, and Unit #: Problem with Unit #

The data is mostly consistent with a comma after the street name; however, because the data was hand entered at one time, there are a few instances where there is no comma.

• ###### 3. Re: Separate a Street Address into 3 fields of St #, St Name, and Unit #: Problem with Unit #

the inconsistancies will pretty much guarantee that you can't get perfect results no matter what you do. Chances are with any method you choose to use, there will be at least a few records where you will need to manually correct unless you choose to simply leave the unit data as part of the street address and not split it off into a separate field.

If I didn't have to separate out the unit number, I'd separate street number from Street Name like this:

Street Number:

LeftWords ( addressField ; 1 )

Street Name:

RightWords ( AddressField ; wordcount (addressfield ) - 1 )

To split off the unit number in all cases where there is a comma between the last word of the street name and the unit number:

Let ( [L = Length ( addressField ) ;
cpos = Position ( AddressField ; "," ; L ; -1 )  //this is a right to left search to find the last comma in the field
];
Trim ( Right ( AddressField ; L - cpos ) )
)

• ###### 4. Re: Separate a Street Address into 3 fields of St #, St Name, and Unit #: Problem with Unit #

Thank you. I will try the calculation you suggested to split off the Unit # from the last word of the St Name that has a comma following the St Name.

• ###### 5. Re: Separate a Street Address into 3 fields of St #, St Name, and Unit #: Problem with Unit #

Unit # should be type Text if  letters are involved and no number calculations are using the data.

• ###### 6. Re: Separate a Street Address into 3 fields of St #, St Name, and Unit #: Problem with Unit #

Nearly perfect news, but still a minor glitch. I used both posts (PhilModJunk [calculation] and Rick Whitelaw [changing Unit # from # to text]) plus what I previously did and found the following to work.

In the example of 42 Washington St, B9:

The St # calculation: Left ( St Add ; 4 ) extracts 42;

The St Name calculation: Filter ( ( St Add )  ; "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz"  &  " " ) extracts Washington St B;

The Unit # calculation: Let ( [ L = Length ( St Add ) ; cpos = Position ( St Add ; "," ; L ; -1 ) ]; Trim ( Right ( St Add ; L - cpos ) ) ) extracts B9.

The good news is that the Unit # B9 is extracting in toto to the Unit #, which it should; however, its letter portion, B, is still carrying to the St Name field, which it should not. How must the St Name calculation be modified to make the B no longer appear in that field?

• ###### 7. Re: Separate a Street Address into 3 fields of St #, St Name, and Unit #: Problem with Unit #

Check my posts again:

I recommended LeftWords ( St Add ; 1 ) for the st #

This works even if the street number exceeds 4 digits or has some accidentally entered spaces in front of the street number.

For St Name, I suggest a more sophisticated version of the example I posted earlier:

If ( Position ( st Add ; "," ; 1 ;1 ) ;
MiddleWords ( St Add ; 2 ; WordCount ( St Add ) - 2 ) ;
RightWords ( St Add ; WordCount ( St Add ) - 1 )
)

This version checks for the presence of a comma in the field. If there is one, it returns the middle word count - 2 words. If it is not present, it returns all but the leftmost word--the street number.

• ###### 8. Re: Separate a Street Address into 3 fields of St #, St Name, and Unit #: Problem with Unit #

Thank you. Your explanations are most helpful. I don't just plop them in, but study them for syntax, so I can learn. I'm leaving for work in about 10 minutes, so I'll use your calculation this evening. I'll let you know how it works.

• ###### 9. Re: Separate a Street Address into 3 fields of St #, St Name, and Unit #: Problem with Unit #

Thank you. It worked.