13 Replies Latest reply on Dec 12, 2016 9:43 AM by ljw338

    How to split land survey location (twp,rg,mrd)?

    ljw338

      New to FileMaker and am trying to split up cells containing well locations, which are originally listed as one string, into 4 separate cells.

       

      Example of original data: S20T31R1M5

      Desired outcome: S20, T31, R1, M5. 

       

      The other issue is that sections (S) and ranges (R) can be either 1 or 2 digits and townships (T) can be either 1, 2 or 3 digits in length.  I have seen answers describing how to split up first and last name into separate cells and that makes sense as they had spaces whereas here I do not.

       

      Any help would be appreciated - thanks in advance.

        • 1. Re: How to split land survey location (twp,rg,mrd)?
          David Moyer

          Hi,

          If you can count on the first value being the S-value, then this should work:

          Substitute(

            TO::OriginalData;

            ["T"; ", T"];

            ["R"; ", R"];

            ["M"; ", M"]

          )

          • 2. Re: How to split land survey location (twp,rg,mrd)?
            philmodjunk

            As long as your different values are always preceded by the same letter, this is pretty easy.

             

            Is the left to right order of these values always, without exception the same and do you always, without exception have all 4 values?

             

            If so:

             

            "S" & GetValue ( Subsitute ( LocationString ; ["R" ; ¶ ] ; [ "T" ; ¶ ] ; [ "M" ; ¶ ] ; [ "S" ; ¶ ] ) ; 1 )

             

            Would return S20 from your example. You can get the other values by changing the quoted letter and the number parameter to different values.

             

            If the left to right order is not always the same, you can use combinations of the Position and Middle functions to parse the text or you can go to a Custom Function web site such as the Brian Dunning site and import a parsing custom function to parse this text for you.

            1 of 1 people found this helpful
            • 3. Re: How to split land survey location (twp,rg,mrd)?
              Tom_Droz

              I suspect that that S is sometimes N etc, the real demarcation is a number followed by a letter.  So example S112T1134N123M23

               

              The first number that gas a letter after is 2, then 4, then 3

               

               

              so you formula needs to take this in to account

              • 4. Re: How to split land survey location (twp,rg,mrd)?
                ljw338

                This looks like the ticket - thank you.  Yes, the order is always the same, left to right.  I really am an extreme beginner... I would replace GetValue with (my field header holding the locations) and LocationString with...?  Is this the correct order (R-T-M-S) that I would want to place the letters within the substitution?  Would that letter order need to change when I change the quoted letter and parameter value?

                • 5. Re: How to split land survey location (twp,rg,mrd)?
                  philmodjunk

                  You would use GetValue as I wrote it. you would replace locationstring with the name of the field that stores the unparsed location text string shown in your original post. The only change that you would make would be to change the value of the quoted letter and the number to get each of the different values. 1 gets you the leftmost value. 2 gets you the next value and so forth.

                   

                  These calculations can be used in calculation field or in set field steps performed by a script.

                  • 6. Re: How to split land survey location (twp,rg,mrd)?
                    ljw338

                    This is great - thanks so much!

                    • 7. Re: How to split land survey location (twp,rg,mrd)?
                      erolst

                      Adding to PMJ's suggestion, here's a deluxe version ...

                       

                      Let ( [

                        letterToParse = "S" ; // specify the sub-string to parse, the rest is "automatic"

                        theList = Substitute [ yourField ; [ "T" ; "¶T" ] ; [ "R" ; "¶R" ] ; [ "M" ; "¶M" ]  ) ;

                        index = Position ( "STRM" ; letterToParse ; 1 ; 1 ) ; 

                        theResult = GetValue ( theList ; index )

                        ] ;

                        theResult

                      )

                      1 of 1 people found this helpful
                      • 8. Re: How to split land survey location (twp,rg,mrd)?
                        philmodjunk

                        And here is a version that will work even if the order is not always the same:

                         

                        Let ( [

                          letterToParse = "S" ; // specify the sub-string to parse, the rest is "automatic"

                          theWords = Substitute [ yourField ; [ "S" ; " S" ] ; [ "T" ; " T" ] ; [ "R" ; " R" ] ; [ "M" ; " M" ]  ) ;

                          index = Position ( theWords ; letterToParse ; 1 ; 1 ) ;

                          theResult = MiddleWords ( thewords ; index ; 1 )

                          ] ;

                          theResult

                        )

                        • 9. Re: How to split land survey location (twp,rg,mrd)?
                          philmodjunk

                          My last example isn't going to work as designed. Time to rethink a bit...

                           

                          Let ( [

                            letterToParse = "S" ; // specify the sub-string to parse, the rest is "automatic"

                            theWords = Substitute [ yourField ; [ "S" ; " S" ] ; [ "T" ; " T" ] ; [ "R" ; " R" ] ; [ "M" ; " M" ]  ) ;

                            start = Position ( theWords ; letterToParse ; 1 ; 1 ) ;

                            end = Position ( theWords ; " " ; start ; 1 ) - 1 ;

                            theResult = Middle ( thewords ; start; End - start + 1 )

                            ] ;

                            theResult

                          )

                          • 10. Re: How to split land survey location (twp,rg,mrd)?
                            erolst

                            philmodjunk wrote:

                            […] even if the order is not always the same

                             

                            Or like this:

                             

                            Let ( [

                              letterToParse = "R" ; // specify the substring to parse, the rest is "automatic"

                              theList = Substitute ( theField ; [ "M" ; "¶M" ] ;  [ "R" ; "¶R" ] ;  [ "S" ; "¶S" ] ;  [ "T" ; "¶T" ] ) ;

                              theList = Right ( theList ; Length ( theList ) - 1 ) ;

                              theIndex = ValueCount ( Left ( theList ; Position ( theList ; letterToParse ; 1 ; 1 ) ) )

                              ] ;

                              GetValue ( theList ; theIndex )

                            )

                            • 11. Re: How to split land survey location (twp,rg,mrd)?
                              Tom_Droz

                              As ljw said, he is a newbie

                               

                              Example of original data: S20T31R1M5

                              Desired outcome: S20, T31, R1, M5. 

                               

                              The data provided is survey locations based on a central point I believe.  So how many rows or townships over and direction.

                              it sounds like he needs to create 4 new fields to capture the data,  So you count characters to the right until you hit the 2nd Letter.   (example above would be 4) then you need to select the first 3 (4-1) characters and set field1 to those 3 characters.

                               

                              Just my opinion

                              • 12. Re: How to split land survey location (twp,rg,mrd)?
                                David Moyer

                                Example of original data: S20T31R1M5

                                Desired outcome: S20, T31, R1, M5.

                                If this was literally the desired outcome, then my answer in post #1 would suffice (except I didn't add a period at the end).

                                • 13. Re: How to split land survey location (twp,rg,mrd)?
                                  ljw338

                                  Thanks a lot - this ended up working the best for me.