5 Replies Latest reply on Jul 19, 2017 1:23 PM by leftear13

    Extracting Data

    leftear13

      Hi,

       

      I am extracting data from a field for a guy who thought it was a good idea to include his Sku# into the ItemName field.

       

      The problem is that the Sku# varies in the number of characters (4 or more) between both letters and numbers separated with a space, so they are not consistent.

       

      Examples Below:

       

      AQ 12100 WINTER IN SCOTLAND

      HB 101 AFRICAN TAPESTRIES: TEMBA

      AWR 53621 THE CLASSICS

      BEAU 16025 HEALING TOUCH

       

      But, what is consistent is that the Sku# always ends before the second space.

       

      Example1 - AQ (space 1) 12100 (space 2) and then the Item Name (WINTER IN SCOTLAND).

      Example2 - HB (space 1) 101 (space 2) and then the Item Name AFRICAN TAPESTRIES: TEMBA

       

      I am looking for help on writing a script where anything before the second spacer, starting from the left, is removed.  Or extract all data after the second space into a new field.  Either or would work, but not sure of the best way to do this, nor do I know how.

       

      Any insight you can offer would be greatly appreciated.

       

      Thanks!

        • 1. Re: Extracting Data
          philmodjunk

          Looks like LefWords (theAboveFieldHere ; 2 )

           

          would work.

          • 2. Re: Extracting Data
            TomHays

            This calculation will pull the pieces apart into SKU and the Item Name.

            The part before the second space will be treated as the SKU.

            The part after the second space will be the item name.

             

             

            Let([

            theString = YourField; // Edit this to use your field name, e.g. the original ItemName field.

            posn2ndSpace = Position(theString; " "; 1; 2); // Locate the second occurrence of space.

            theSKU = Left(theString; posn2ndSpace - 1);

            theItemName = Right(theString; Length(theString) - posn2ndSpace)

            ];

            theSKU & "|" & theItemName

            )

             

            I made the calculation return both values separated by a vertical line.  Edit to return one or the other as needed.

             

             

            AQ 12100 WINTER IN SCOTLAND --> AQ 12100|WINTER IN SCOTLAND

            HB 101 AFRICAN TAPESTRIES: TEMBA --> HB 101|AFRICAN TAPESTRIES: TEMBA

            H-B 102 EASY CHAIR --> H-B 102|EASY CHAIR

             

             

            -Tom

            • 3. Re: Extracting Data
              leftear13

              That displays the first 2 words only.  I want to remove the first two words and display anything after starting with the third word.

              • 4. Re: Extracting Data
                TomHays

                MiddleWords(theAboveFieldHere; 3, WordCount(theAboveFieldHere))

                 

                Will skip over the first two words and return everything after that starting with the 3rd word.

                 

                -Tom

                1 of 1 people found this helpful
                • 5. Re: Extracting Data
                  leftear13

                  Thanks all for the help!