How did this problem come to exist?
City, State, and Zip should already START in separate fields.
CityStateZip would then be a calculated result.
I agree with Bruce's puzzlement and await your explanation. If, however, you actually have all this in a single field, some further questions:
1. is all the text on a single line? (If not, you have carriage returns to assist you.)
2. are city names the only text that may contain two words? (Many state names use multi-word format. District of Columbia, Australian Capital Territory, to name just two)
3. can you reliably and correctly extract the zip? (should be easy if it's all numerals)
4. can you reliably and correctly extract the state? (should be easy if abbreviations are used … DC, ACT, etc)
5. if you can do 3 and 4, whatever is left should be the city …
1 of 1 people found this helpful
Left ( CityStateZip ; Position (CityStateZip ; ", "; 1 ; 1 ) - 1 )
you appear to to have been looking at very old code given the commas and missing parameter in the position function.
pasted in whole line from address block where i have to separate name and address into separate fields.
found the code in the Filemaker resources, no telling how old it is
I can get the zip and the city of only one word but cannot extract the state; does anyone have the correct code to extract the middle value of two letters
What I posted should give you all text to the left of the comma. Assuming this format:
City, State abbreviation Zip
That should return the city no matter how many words make up the name of the city.
Let ( [ commaPos = Position (CityStateZip ; ", "; 1 ; 1 ) ;
L = Length ( CityStateZip ) ] ;
LeftWords ( Right ( CityStateZip ; L - CommaPos ) ; 1 )
) // Let
Note that there are a number of custom functions available on custom function sharing sites designed specifically for parsing text. You may also want to research them. Even if you do not have advanced, you can often adapt the calculations used in the custom function into either a straightforward calculation or a looping scrpt.