4 Replies Latest reply: May 15, 2014 9:55 AM by Data Kruncher RSS

    Text extraction

    Bradl Vogl

      I have a data field that starts with the letter "B" the majority of times.

      I need to remove that single beginning "B".

      Occasionally, the first character is not "B" at which time I need to retain all characters.

      I tried:

       

      Substr(,2,17)

       

      Of course, that cuts off the non "B" characters which I want to retain.

      How can I say remove the first "B" only if the data begins with "B", otherwise retain as is ?

       

      Example of desired result:

      [font="courier"]From                  To

      B1234-34              1234-34

      BB65HYV               B65HYV

      B45678                45678

      LWC-TCL-004           LWC-TCL-004[/font][/quote]Any help would be appreciated....

       

      Thanks,

      Brad Vogl

        • Text extraction
          Data Kruncher

          Hi Brad,

           

          For this type of requirement I like to add a "Pre " prefix to the variable so that I know immediately that a calculation will be made using this field.

           

          Keeping this in mind, let's rename your initial field to "Pre List Assy P/N".

           

          Now create a calculated field named "List Assy P/N" and give it this formula:

          [font="courier"]If(Left(,1)="B",Substr(,2,17),[Pre List Assy P/N])  /font[/quote]Which basically says "if the first character of the field is "B", then use only the remainder of the field, otherwise it isn't "B", so use the whole field. Assuming that your value of 17 is large enough. You can CYA by increasing the 17 to something absurd like 100 and Monarch won't complain and you'll never have to revise the formula if the original field expands.

           

          HTH,

           

          Kruncher

           

          Edited to add forgotten ")" in the formula.

          • Text extraction
            Bradl Vogl

            Kruncher,

                That worked great!

                Thanks for the help.

             

            Brad Vogl

            • Text extraction
              Nick Osdale-Popa

              Just as an aside, instead of having to try to figure out if you need to use 17, 100, or 1000 characters, the formula can be adjusted as such and never touched again:

               

              [font="courier"]If(Left(,1)="B",Substr(,2,Len()),[Pre List Assy P/N])  /font[/quote]The LEN() formula will always adjust to the length of your field.

               

              Just my 2c worth!  

              • Text extraction
                Data Kruncher

                Nice tweak Nick. Thanks for the input. Oh, and glad to hear your results Brad.