4 Replies Latest reply on Nov 17, 2010 2:54 AM by daveealex

    Remove item from field

    daveealex

      Title

      Remove item from field

      Post

      I have a field that has text like this:

      Name one ---Ford Fiesta---

      Name two ---FIAT PANDA---

      Name three ---Alfa Romero 159---

      The data has a name or business then ---,  a name of a car and --- again. The name sizes vary and so does the data in between the dashes.

      I need to remove the ---and the data here and again the trailing--- but keep the data before the first ---.

      I just cant seem to find a calculation/script to do it. I would like to keep the original field intact if possible and have a new field with the correct data.  I hope I am clear

      Dave

        • 1. Re: Remove item from field
          philmodjunk

          "I need to remove the ---and the data here and again the trailing--- but keep the data before the first ---."

          But your example data doesn't have any data before the first --- as those are the first three characters entered in the field...

          If you want just Ford Fiesta returned from the above example. You can nest some functions to extract just that text:

          Left ( TextField ; Position (TextField ; "---" ; 1 ; 2 ) - 1 )

          Will return ---Ford Fiesta, so we just need one more function to strip off the leading dashes.

          Let ( S = Left ( TextField ; Position (TextField ; "---" ; 1 ; 2 ) - 1 ) ; Right ( S ; Length ( S ) - 3 ) )

          • 2. Re: Remove item from field
            daveealex

            Phil thanks for the answer

            there is data before the first --- it has a name of a business and then the first ---. The amount of words and characters before the first --- is random and has no pattern. I just want to delete the --- ford fiesta--- and leave all the data before the ---ford fiesta--- alone.

            David

            • 3. Re: Remove item from field
              philmodjunk

              In that case:

              Left ( TextField ; Position (TextField ; "---" ; 1 ; 1 ) - 1 )

              will return everything to the left of the first --- in the field.

              • 4. Re: Remove item from field
                daveealex

                Thanks Phil, it worked perfectly!!