If you know the filemaker word seperators you can use the right, middle, left word functions.
For example LeftWords( "2322 Lexington Avenue, Columbus, Ohio 43211" ; 4 ) would return "2322 Lexington Avenue, Columbus,"
If the address ALWAYS has commas separating the street, city, state+zip values then it's pretty easy: substitute commas for paragraph marks.
value 1 is street
value 2 is city
value 3 is state + zip
To separate the state and zip code: the zip is the first word from the right of the value.
If the addresses have more or less than 2 commas then they will need to be treated differently. I'd process all the address with exactly 2 commas first and then see how many are left. Those remaining might need to be processed by hand.
Still not sure how I would do it in which filed, I want to put each one I separate into another field. I want to take street address and put it in street field, city in city field etc. I don't know how to setup the fields to do this. I lokked all over the internet and it explains what to do but not how to setup the calculations in what fields and how to get all the records done.
If the full address is in a field called "address_full" then these calcs will do it for you:
getvalue( substitute( address_full ; ", " ; "¶" ) ; 1 )
getvalue( substitute( address_full ; ", " ; "¶" ) ; 2 )
leftwords( getvalue( substitute( address_full ; ", " ; "¶" ) ; 3 ) ; wordcount( getvalue( substitute( address_full ; ", " ; "¶" ) ; 3 ) - 1 ) )
rightwords( getvalue( substitute( address_full ; ", " ; "¶" ) ; 3 ) ; 1 )
Kevin, it would be most helpful if you posted the calculations you found (that did not work), so that we may:
help you fix (or...)
point you to better calculations
Exactly what I needed! Just a couple to fix that had a PO Box & Suite but no problem.
Thank You Very Much!
Now that the fields are there and I try to modify it comes up and says they can't be modified. Is there a way they can be stored or allready are bur I need to do something else now?
You can change the calc fields to text, and make the calculations into auto-enter.
To simplify Vaughan's answer (and make it both faster and easier to maintain), you might want to look at using the Let function.
Let ( [
addressAsList = Substitute ( address_full ; ", " ; "¶" ) ;
street = GetValue ( addressAsList ; 1 ) ;
city = GetValue ( addressAsList ; 2 ) ;
line3 = GetValue ( addressAsList ; 3 ) ;
state = LeftWords ( line3 ; WordCount ( line3 ) - 1 ) ;
zip = RightWords ( line3 ; 1 )
// Insert whichever variable you need for the particular case
To deal with the "can't modify" issue, use a text field with an auto-enter calculation instead of a calculation field.