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 #.
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.
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:
LeftWords ( addressField ; 1 )
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 ) )
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.
Unit # should be type Text if letters are involved and no number calculations are using the data.
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?
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.
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.
Thank you. It worked.