6 Replies Latest reply on Jun 10, 2016 3:18 PM by erolst

    Need to locate state abbreviations within a text field

    jandersen316

      I'm trying to parse a notes field from another database that has billing address info in random locations within the notes. The end goal to extract the entire mailing address as separate fields (Street, City, State, Zip) from the original notes. The state abbreviation in the original notes field is always two letters, always in CAPS, and is always followed by the zip code. But I can't seem to do this using PatternCount. I need to find only the two-letter combinations that are capitalized,, but PatternCount won't get that specific. Example:

       

      In the following text string I want to isolate 'SC' (the abbreviation for South Carolina). So I've created a long calculation that looks for every two-letter state abbreviation. The hang-up is that 'Mt' (re: Mt Pleasant), is also a state abbreviation (for Montana), so when I perform the calculation on the following text string:

       

      1044 Johnnie Dodds Blvd #L3

      Mt Pleasant SC 29464

       

      Filemaker returns 'Mt'. I need a calculation that finds only the two-letter upper case string for each state abbreviation, and PatternCount alone won't do it. I've tried using the == modifier within the calculation, but it seems that Filemaker wants to find a literal "==SC". I've also tried using the Upper() function...so far no success. Any ideas?

       

      Here are a few of the 50+ lines of code I'm using (unsuccessfully) to find the state (and Canadian province) abbreviations:

       

      Case(

       

      PatternCount(OldDBNotes; Upper("MT ")) = 1; Position(OldDBNotes; Upper("MT "); 1;1);

      PatternCount(OldDBNotes; Upper("SC ")) = 1; Position(OldDBNotes; Upper("SC "); 1;1);

      PatternCount(OldDBNotes; Upper("WA ")) = 1; Position(OldDBNotes; Upper("WA "); 1;1);           

       

      ...etc., etc., for 50+ 2-letter abbreviations.

       

      I still get 'Mt' returned on my sample address, with or without the Upper() qualifier.

       

      (Yes, I know this calc will return a numerical position and not the actual state abbreviation characters - but that's OK. Once I have the position, extracting the two-letter abbreviation itself is cake).

       

      Any ideas? I'd be much obliged.

        • 1. Re: Need to locate state abbreviations within a text field
          erolst

          jandersen316 wrote:

          The state abbreviation in the original notes field is always two letters, always in CAPS, and is always followed by the zip code.

           

          Then you could use

           

          MiddleWords ( OldDBNotes ; WordCount ( OldDBNotes ) - 1 ; 1 )

           

          provided you already managed to isolate that block.

           

          Alternatively, try a Custom Function:

           

          // FindStateAbb ( theText )

          Let ( [

            $i = $i + 1 ;

            textAsList = Substitute ( theText ; " " ; ¶ ) ;

            listOfFoundAbbs = FilterValues ( textAsList ; "MT¶SC¶WA" ) ; // etc. … or create a list of State abbreviations as value list, Custom Function or in its own table – always handy to have, I gather – and pass a reference here

            countOfFoundAbbs = ValueCount ( listOfFoundAbbs ) ;

            thisFoundAbb = GetValue ( listOfFoundAbbs ; $i )

            ] ;

            Case ( Exact ( thisFoundAbb ; Upper ( thisFoundAbb ) ) ;

            Let ( $i = "" ; thisFoundAbb ) ;

            Case ( $i < countOfFoundAbbs ; FindStateAbb ( theText ) )

            )

          )

           

          or create a script out the logic – straight looping is easier than recursion.

          • 2. Re: Need to locate state abbreviations within a text field
            siplus

            Let ([

             

            is = "1044 Johnnie Dodds Blvd #L3

            Mt Pleasant SC 29464";     // whatever your address is; is also means isolate state

             

            f1 = Filter(Substitute(is;" ";¶); "ABCDEFGHIJKLMNOPQRSTUVWXYZ¶");

             

            myStateList = List("AA";"AB";"SC")    /* ...... */  ];     // all the state codes inhere, can be calced outside and referenced

             

            FilterValues(myStateList ; f1)

             

             

            )

            • 3. Re: Need to locate state abbreviations within a text field
              jandersen316

              You've lost me...how does this calc tell me where the two capital letters 'SC' are located in the OldDBNotes field? And how does it prevent me from finding 'Mt' instead of 'SC'?

              • 4. Re: Need to locate state abbreviations within a text field
                erolst

                Would that be the first calc or the second one?

                • 6. Re: Need to locate state abbreviations within a text field
                  erolst

                  Well, I amended my post; so the idea is that if you are able to isolate the address block, then you could pull out the abbreviation by position – whatever that abbreviation may be.

                   

                  All in all, you're not really interested in the position - that is only a means to an end. You want to get that abbreviation.

                   

                  But I think the second calculation is better, because it could be applied to whatever you feed it – if there is a standalone two-letter string that matches one of the abbreviations and is in UPPERCASE, it is returned – or more to the point, the first occurrence of such a beast is returned (so you will get SC, not Mt).

                   

                  PS: Siplus's solution is better, because he uses Filter() to chop away the lower-case stuff, and thus needs no recursion.

                   

                  Good idea …