5 Replies Latest reply: Mar 4, 2016 10:50 PM by John Jackson RSS

    Removing numbers from a Text String

    John Jackson

      Hi Guys I need your help removing 2 numerical values as 2 diff fields from one Text String. Pls see below:

       

       

      Field Text
      month is november 70 % work done accounting age group < 3  Finance age Group < 5 fiscal year 2021
      month is november 70 % work done accounting age group < 3  Finance age Group > 5 fiscal year 2021
      month is november 70 % work done accounting age group = 3  Finance age Group = 5 fiscal year 2021
      70 % work done accounting age group < 1100 fiscal year 2021 monthend invoice accounts payable Finance age Group > 500
      71 % work done accounting age group < 1600 fiscal year 2021 monthend invoice accounts payable Finance age Group > 20

       

      each row has number located after 'Finance age Group <' or 'accounting age group <'  with the exception '<' or '>' can be '='

      so the jist of what i want is;

      the numbers I need removed are:

      1) accounting age group < 3 : I need '3' as a number 3 parsed out in a caluclated field Accounting Age

      2) Finance age Group < 5 : I need '5' as a number 5 parsed out in a caluclated field Finance Age


      Thanks so much I really appreciate

        • Re: Removing numbers from a Text String
          Stefan Wühl

          Maybe something like

           

          Textbetween( Replace(TEXTFIELD, '=','<'), 'Finance age Group < ',' ') as FinanceAge

          • Re: Removing numbers from a Text String
            Sunny Talwar

            May be too customized, but seem to work for the provided dataset:

             

            Table:

            LOAD Text,

              KeepChar(Left(TextBetween(Text, 'accounting age group', 'Finance age Group'), 8), '1234567890') as [Accounting Age],

              KeepChar(Left(SubField(Text, 'Finance age Group', 2), 8), '1234567890') as [Finance Age];

            LOAD * Inline [

            Text

            month is november 70 % work done accounting age group < 3  Finance age Group < 5 fiscal year 2021

            month is november 70 % work done accounting age group < 3  Finance age Group > 5 fiscal year 2021

            month is november 70 % work done accounting age group = 3  Finance age Group = 5 fiscal year 2021

            70 % work done accounting age group < 1100 fiscal year 2021 monthend invoice accounts payable Finance age Group > 500

            71 % work done accounting age group < 1600 fiscal year 2021 monthend invoice accounts payable Finance age Group > 20

            ];


            Capture.PNG

            • Re: Removing numbers from a Text String
              Sebastian Lettner

              Hi,

               

              this should work for you.

               

              Source:
              LOAD * INLINE
              [
                  FieldText
                  month is november 70 % work done accounting age group < 3  Finance age Group < 5 fiscal year 2021
                  month is november 70 % work done accounting age group < 3  Finance age Group > 5 fiscal year 2021
                  month is november 70 % work done accounting age group = 3  Finance age Group = 5 fiscal year 2021
                  70 % work done accounting age group < 1100 fiscal year 2021 monthend invoice accounts payable Finance age Group > 500
                  71 % work done accounting age group < 1600 fiscal year 2021 monthend invoice accounts payable Finance age Group > 20
              ];
              
              
              Out:
              LOAD
                  FieldText,
                  left(aagRP, 1) as aagSign,                                                    //Sign after accounting age group
                  left(fagRP, 1) as fagSign,                                                    //Sign after Finance age Group
                  Mid(aagRP, 2, if(aagNE = 0, Len(aagRP) + 1, aagNE) - 2) as aagNum,            //Number after accounting age group
                  Mid(fagRP, 2, if(fagNE = 0, Len(fagRP) + 1, fagNE) - 2) as fagNum;            //Number after Finance age Group
              LOAD
                  *,
                  index(aagRP, ' ', 2) as aagNE,
                  index(fagRP, ' ', 2) as fagNE;
              LOAD
                  *,
                  right(FieldText, Len(FieldText) - aagStart - 20) as aagRP,    //Right part after accounting age group
                  right(FieldText, Len(FieldText) - fagStart - 17) as fagRP;    //Right Part after Finance age Group
              LOAD
                  FieldText,
                  index(FieldText, 'accounting age group') as aagStart,        //Start index of accounting age group
                  index(FieldText, 'Finance age Group') as fagStart            //Start index of Finance age Group
              Resident Source;
              

               

              Regards

              Sebastian Lettner

              • Re: Removing numbers from a Text String
                Peter Cammaert

                It seems I don't need to stay up at night anymore: parse a numerical value from a string text

                 

                Care to close that discussion if you start another one?