11 Replies Latest reply on Aug 15, 2017 1:52 PM by J_File

    Getting all text after an underscore

    smac11shl

      I am looking to find a calulation that will give me all of the text after an underscore, for example:

       

      If I have a field and the text is ORD_SHP_20120208, I would like to get all of the text after the second underscore. With this example I can use many functions such as left, right, LeftWords, etc... however, what is the first two words vary in length? Or the last section is more than one word?

       

      So I would have

       

      Record 1:

      ORD_SHP_20120208 --> Result: 20120208

       

      Record 2:

      ORD_SHP_20120208 9384 --> Result: 20120208 9384

       

      Record 3:

      ORD_SHP9323_20120208 8475 --> Result: 20120208 8475

       

       

      I would like to be able to use the replace function to get the last section in each record - so I am assuming I would need some sort of calculation.

       

      Any Ideas?

       

      Thank you in advance!

        • 1. Re: Getting all text after an underscore
          comment

          Try =

           

          Right ( Textfield ; Length ( Textfield ) - Position ( Textfield ; "_" ; 1 ; 2 ) )

          • 2. Re: Getting all text after an underscore
            IreneVassos

            Here is a more extensible way to find the last unique characters in your string -- there may be MORE than one or two (or there could be none!).  An example using the Let Function and variables in your calculation. Hope this helps. 

             

            Irene

             

                 Let ( [

                 //---Define the unique character you are looking for; you can always substitute another character between the quotes

                   $Character = "_";

                 //---How long is the text string       

                    $Len    = Length (YourText);

             

                  //---How many unique characters are in the text string

                   $Num   = PatternCount (YourText) ; $Character);

             

                 //---Were is the position of the last character in the string

                    $Where = Position (YourText); $Character ; 1 ; $Num )


                 ] ;

             

                 //---Calculation - show the characters after the last unique characters, if there are any, otherwise return nothing

                     Case ($Num > 0; 

                               Right (YourText; $Len - $Where);   "" )

             

                  )

            • 3. Re: Getting all text after an underscore
              comment

              IreneVassos wrote:

               

                   //---Were is the position of the last character in the string

                      $Where = Position (YourText); $Character ; 1 ; $Num )

               

              How about =

               

              Position ( text ; char ; Length ( text) ; -1 )

               

               

              BTW, you should not define $variables in a calculation - unless you need them elsewhere.

              • 4. Re: Getting all text after an underscore
                wsvp

                Here is another option... The "TEST_DATA_01 would be the data in...

                 

                Let ( [

                 

                 

                S = TEST_DATA_01 ;

                L = Substitute ( S ; "_" ; "¶" ) ;

                C = ValueCount ( L ) ;

                R = GetValue ( L ; C )

                 

                 

                ] ; R )

                • 5. Re: Getting all text after an underscore
                  RayCologon

                  wsvp wrote:

                   

                  Here is another option... The "TEST_DATA_01 would be the data in...

                   

                  Let ( [

                   

                   

                  S = TEST_DATA_01 ;

                  L = Substitute ( S ; "_" ; "¶" ) ;

                  C = ValueCount ( L ) ;

                  R = GetValue ( L ; C )

                   

                   

                  ] ; R )

                   

                  Hi wsvp,

                   

                  The first problem I can see with that approach is that the original poster said s/he "would like to get all of the text after the second underscore", however your method will return all the text after the last underscore (which may or may not be the second underscore).

                   

                  That aside, when recommending an alternative method, there would be some value in saying why you consider it might be preferable (assuming you do) - and if you don't, then it would probably be a good idea to say that as well...

                   

                  Also, it's not clear to me why you've formed the syntax to declare everything as a variable. The entirety of your calculation amounts to:

                   

                  Let([

                  L = Substitute(TEST_DATA_01; "_" ; ¶);

                  C = ValueCount(L)];

                  GetValue(L; C)

                  )

                   

                  Declaring four variables rather than two in this case doesn't make the calculation any more efficient, and doesn't seem to make it any easier to write or read either, so I'm curious as to why you'd recommend it.

                   

                  Regards,

                  Ray

                  ------------------------------------------------

                  R J Cologon, Ph.D.

                  FileMaker Certified Developer

                  Author, FileMaker Pro 10 Bible

                  NightWing Enterprises, Melbourne, Australia

                  Email: cologon@nightwingenterprises.com

                  http://www.nightwingenterprises.com

                  ------------------------------------------------

                  • 6. Re: Getting all text after an underscore
                    beverly

                    Hey, smac11sh1! you've gotten several good answers. In FileMaker there is always more than one way to do most everything. In an effort to "impart knowledge" of the product functionality, I'm going to give one more. You make a choice of which to use (or combine them). And perhaps another person might learn from the examples.

                     

                    IF the field always has "ORD_SHP_" as the first part of the value and you want what's after that:

                     

                    the Middle() function can also be used to get the "right" of a string with the use of two other functions:

                         Middle(text;start;numberOfCharacters)

                         Position(text;searchString;start;occurrence)

                         Length(fieldOrString)

                     

                         Middle ( field ; Position ( field; "_"; 1; 2 ) + 1 ; Length ( field ) )

                     

                    Middle can take the last parameter as the entire length and only select from the starting position through the last character. While in some programming, this may throw an error for exceeding the number of characters, it's perfectly ok here.

                     

                    [I've also done this: Middle ( field ; Position ( field; "ORD_SHP_"; 1; 1; ) + Length ( "ORD_SHP_" ) ; Length ( field ) ). Sometimes it helps to see what's "constant" to understand these functions.]

                     

                    Just hoping this tip helps someone! Use the functions (as in other replies) inside a "Let ()" statement, if so desired.

                    Beverly

                     

                    created by smac11shl in Scripting and Calculations - View the full discussion

                    I am looking to find a calulation that will give me all of the text after an underscore, for example:

                     

                    If I have a field and the text is ORD_SHP_20120208, I would like to get all of the text after the second underscore.  With this example I can use many functions such as left, right, LeftWords, etc... however, what is the first two words vary in length? Or the last section is more than one word?

                     

                    So I would have

                     

                    Record 1:

                     

                    ORD_SHP_20120208 --> Result: 20120208

                     

                    Record 2:

                     

                    ORD_SHP_20120208 9384 --> Result: 20120208 9384

                     

                    Record 3:

                     

                    ORD_SHP9323_20120208 8475 --> Result: 20120208 8475

                     

                    I would like to be able to use the replace function to get the last section in each record - so I am assuming I would need some sort of calculation.

                    Any Ideas?

                    Thank you in advance!

                     

                    • 7. Re: Getting all text after an underscore
                      wsvp

                      Thanks Ray...

                       

                      I think I may have interpreted the question a little bit differently than you.  I made the assumption that the underscores were part of the field names, followed by an underscore. and they were looking for the tailing data… I assumed the word "Second" was related to the example shown and that there could be field names with more underscores.

                       

                      The reason I formatted the "Let" the way I did is probably more of a habbit for me as I often wind up writing long involved Let's that I re-use and alter 1 or more of the variables to get a different result.

                       

                      I don't necessarily think this method is any better than the other options… I was just throwing it in as another approach.  I also think every one of us interpreted the question a little differently.

                       

                      Thanks,

                       

                      Mike West

                      WSVP

                      • 8. Re: Getting all text after an underscore
                        Stephen Huston

                        IF all characters before that underscore are letters or underscores, and all characters following that underscore are numbers:

                         

                        • GetAsNumber (field)

                         

                        That will do it, IF....

                         

                        Stephen Huston

                        • 9. Re: Getting all text after an underscore
                          Stephen Huston

                          But maybe not, as it looks like you may want to preserve some spaces.

                           

                          If so you could filter the field on characters [ "0 123456789"  with a space in the string].

                          • 10. Re: Getting all text after an underscore
                            RayCologon

                            wsvp wrote:

                             

                            Thanks Ray...

                             

                            I think I may have interpreted the question a little bit differently than you.  I made the assumption that the underscores were part of the field names, followed by an underscore. and they were looking for the tailing data… I assumed the word "Second" was related to the example shown and that there could be field names with more underscores.

                             

                            The reason I formatted the "Let" the way I did is probably more of a habbit for me as I often wind up writing long involved Let's that I re-use and alter 1 or more of the variables to get a different result.

                             

                            I don't necessarily think this method is any better than the other options… I was just throwing it in as another approach.  I also think every one of us interpreted the question a little differently.

                             

                            Thanks,

                             

                            Mike West

                            WSVP

                             

                            Hi Mike,

                             

                            Thanks for clarifying. I hadn't taken the ORD_SHP part of the strings in the original post to refer to field names, so you're right that we're reading it differently - and you're right that the replies indicate various interpretations and come at the problem from a number of angles.

                             

                            No problem regarding the methodology - I was just curious about the reasoning behind it.

                             

                            Cheers,

                            Ray

                            ------------------------------------------------

                            R J Cologon, Ph.D.

                            FileMaker Certified Developer

                            Author, FileMaker Pro 10 Bible

                            NightWing Enterprises, Melbourne, Australia

                            http://www.nightwingenterprises.com

                            ------------------------------------------------

                            • 11. Re: Getting all text after an underscore
                              J_File

                              Irene, an old thread, but your reply to the OP allowed me to extract a filename from a file path name (using the "/" as the separator).

                               

                              Thanks ever so much!

                               

                              J