5 Replies Latest reply on May 20, 2017 2:10 PM by philmodjunk

# separating state  from citystatezip formula does not work   ie  Middle (CityStateZip,Position (CityStateZip,", ",1)   2, 2)

does anyone have an answer if city is one or two words?

formula given in knowledge base does not function at all

Middle (CityStateZip,Position (CityStateZip,", ",1)   2, 2)

• ###### 1. Re: separating state  from citystatezip formula does not work   ie  Middle (CityStateZip,Position (CityStateZip,", ",1)   2, 2)

How did this problem come to exist?

City, State, and Zip should already START in separate fields.

CityStateZip would then be a calculated result.

• ###### 2. Re: separating state  from citystatezip formula does not work   ie  Middle (CityStateZip,Position (CityStateZip,", ",1)   2, 2)

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 …

• ###### 3. Re: separating state  from citystatezip formula does not work   ie  Middle (CityStateZip,Position (CityStateZip,", ",1)   2, 2)

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.

1 of 1 people found this helpful
• ###### 4. Re: separating state  from citystatezip formula does not work   ie  Middle (CityStateZip,Position (CityStateZip,", ",1)   2, 2)

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

• ###### 5. Re: separating state  from citystatezip formula does not work   ie  Middle (CityStateZip,Position (CityStateZip,", ",1)   2, 2)

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.