10 Replies Latest reply on Jun 14, 2017 11:46 AM by slickslack

    Delete text before X

    firebase

      I have text like this

       

      abc- defg

      hij- klm

      nop- qrs

       

      and so on.

       

      I want to delete everything before "-" and inkluding the "-".

       

      So after taht it should look like

       

      defg

      klm

      qrs

       

      Any ideas?

        • 1. Re: Delete text before X
          steveromig

          I have text like this

           

          abc- defg

          hij- klm

          nop- qrs

           

          and so on.

           

          I want to delete everything before "-" and inkluding the "-".

           

          So after taht it should look like

           

          defg

          klm

          qrs

           

          Any ideas?

           

          If it is always this format and there is a space after the "-" then the simplest method would be to perform a "replace field contents" on your "text field" using...

           

          MiddleWords ( Text FIeld ; 2 ; 1 )

           

          If there could be other format combinations you will have to get a little more creative in how you perform the replace - assuming you want to replace the content above with the modified shorter version. 

           

          If you want to keep the original data then you can create an additional text field and use the calculation above to populate the new text field.

           

          Steve Romig

          FileMaker, Inc.

          • 2. Re: Delete text before X
            firebase

            Sorry, my mistake.

             

            It can vary like:

             

            bc- defg

            klm

            nop- qrs

            1- qrs

             

            So if  there is a "-" in each line delete everything before that.

             

            Would be the right task.

            • 3. Re: Delete text before X
              steveromig

              Sorry, my mistake.

               

              It can vary like:

               

              bc- defg

              klm

              nop- qrs

              1- qrs

               

              So if  there is a "-" in each line delete everything before that.

               

              Would be the right task.

               

              In that "case" you could wrap a case statement around it similar to...

               

              Case ( PatternCount ( Text Field ; "-" ) > 0 ; MiddleWords ( Text Field ; 2 ; 1 ); Text Field)

               

              The PatterCount function checks to see if there is a "-" in your data string and if it finds one then it takes the "second word" and if it doesn't find one then it takes the string as is.

               

              Any more format combinations?

               

              Steve Romig

              FileMaker, Inc.

              • 4. Re: Delete text before X
                Nehme

                If you wish to do it using a script, you can try the following:

                Assuming the text is in a field called "field"

                 

                SetVariable [$Field, field  ]

                SetVariable [$Count, ValueCount ( field) ]

                SetVariable [$i, 1 ]

                Loop

                SetVariable [$Trim, Right ( GetValue ( $Field ; $i )   ; Length ( GetValue ( $Field ; $i ) ) -  Position ( GetValue ( $Field ; $i ) ; "-"  ; 1 ; 1 ) ) ]

                SetVariable [$Value, Case ( not IsEmpty ( $Value ) ;  $Value & "¶" & $Trim ; $Trim ) ]

                SetVariable [$i, $i + 1 ]

                Exit Loop If [$i > $Count ]

                End Loop

                 

                If you want to remove also the space, just put "- " instead of "-"

                 

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

                 

                If you want to do it using a calculation only, you must use custom functions.

                 

                Hope this helps

                • 5. Re: Delete text before X
                  erolst

                  You can simplify that script to

                   

                  Set Variable [ $field ; Table::field ]

                  Set Variable [ $count, ValueCount ( $field ) ]

                  Loop

                    Exit Loop If [ Let ( $i = $i + 1 ; $i > $count ]

                    Set Variable [ $line ; Let ( [ thisLine = GetValue ( $field ; $i ) ; thisLine = Substitute ( thisLine ; "- " ; ¶ ) ] ; GetValue ( thisLine ; 2 ) ) ]

                    Set Variable [ $result ; List ( $result ; $line ) ]

                  End Loop


                  or as a CF:


                  // Clean ( input ) =

                  Let ( [

                  line = GetValue ( input ; 1 ) ;

                  $res = List ( $res ; GetValue ( Substitute ( line ; "- " ; ¶ ) ; 2 ) ) ;

                  rem = MiddleValues ( input ; 2 ; ValueCount ( input ) - 1 )

                    ] ;

                    Case ( Length ( rem ) ; Clean ( rem ) ; $res & Let ( $res = "" ; "" ) )

                  )

                  • 6. Re: Delete text before X
                    Extensitech

                    Would this work?

                     

                    Let ( [

                    string = "slkd -777" // put field name here

                    ] ;

                    Right ( string ; Length ( string ) - Position ( string ; "-" ; 1 ; 1 ) + 1 )

                    )

                     

                    If you really do mean "x", you could make the dash a parameter in the let, too, or make them both parameters of a custom function.

                     

                    Chris Cain

                    Extensitech

                    • 7. Re: Delete text before X
                      user19752

                      There is an unclear point that the text 4 lines are in a record, or each 1 line is in a record.

                      • 8. Re: Delete text before X
                        slickslack

                        Relatively new to FM.

                        trying to understand these solutions above for my own use.

                         

                        I have a bunch of 1000 record DBs made from imported EDL video lists.

                        I need to clean up a description field.

                        Basically delete everything before the word SOURCE in fields like this:

                         

                        RECORD 1    "M2   209_0079       030.0                19:09:56:23 SOURCE FILE: 209_0079"

                        RECORD 2     "M2   A008_C01       030.0                12:51:22:13 SOURCE FILE: A008_C016_06204P"

                         

                        And turn it into this:

                        RECORD 1    "SOURCE FILE: 209_0079"

                        RECORD 2     "SOURCE FILE: A008_C016_06204P"

                         

                        Sometime the tape name after M2 has spaces in it and sometimes other more complicated text names.

                         

                        Thanks

                        • 9. Re: Delete text before X
                          philmodjunk

                          There are lots of ways to parse text in FileMaker. You can also find custom functions on custom function sharing sites that make it simpler.

                           

                          But in this case:

                           

                          Let ( [ T = YourField here ;

                                   L = Length ( T ) ;

                                   P = Position ( T ; "Source" ; 1 ; 1 ) ] ;

                                   Right ( T ; L - P + 1 )

                                 ) // Let

                          1 of 1 people found this helpful
                          • 10. Re: Delete text before X
                            slickslack

                            That did it well enough to free up a few hours for other tasks. Thanks so much.