8 Replies Latest reply on Jun 2, 2016 4:16 AM by Mike_Mitchell

    How To Separate out a Address Filed

    KevinRoach62

      Hello All!

       

      I am using FM11, How can I separate out this address field? 2323 Lexington Avenue, Columbus, Ohio 43211

       

      I need to have Street, City, State & Zipcode in separate fields. I have tried some calculations I found from various places but didn't work.

       

      Thanks,

      Kevin

        • 1. Re: How To Separate out a Address Filed
          coherentkris

          If you know the filemaker word seperators you can use the right, middle, left word functions.

          Word separators in FileMaker Pro | FileMaker

          For example LeftWords( "2322 Lexington Avenue, Columbus, Ohio 43211" ; 4 ) would return "2322 Lexington Avenue, Columbus,"

          • 2. Re: How To Separate out a Address Filed
            Vaughan

            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.

            • 3. Re: How To Separate out a Address Filed
              KevinRoach62

              Ok!

               

              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.

               

              Thanks,

              Kevin

              • 4. Re: How To Separate out a Address Filed
                Vaughan

                If the full address is in a field called "address_full" then these calcs will do it for you:

                 

                street =

                getvalue( substitute( address_full ; ", " ; "¶" ) ; 1 )

                 

                city =

                getvalue( substitute( address_full ; ", " ; "¶" ) ; 2 )

                 

                state =

                leftwords( getvalue( substitute( address_full ; ", " ; "¶" ) ; 3 ) ; wordcount( getvalue( substitute( address_full ; ", " ; "¶" ) ; 3 ) - 1 ) )

                 

                zip =

                rightwords( getvalue( substitute( address_full ; ", " ; "¶" ) ; 3 ) ; 1 )

                • 5. Re: How To Separate out a Address Filed
                  beverly

                  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

                  beverly

                  • 6. Re: How To Separate out a Address Filed
                    KevinRoach62

                    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?

                     

                    Thanks Again!

                    Kevin

                    • 7. Re: How To Separate out a Address Filed
                      Vaughan

                      You can change the calc fields to text, and make the calculations into auto-enter.

                      • 8. Re: How To Separate out a Address Filed
                        Mike_Mitchell

                        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.

                         

                        HTH

                         

                        Mike