8 Replies Latest reply: May 15, 2014 10:08 AM by Nick Osdale-Popa RSS

    formula question

    adonis _

      is there a way to use monarch to create a calculated field which would take

      the last 8 digits and print the 8 only when there is 8 digits, if the following item below is not 8 digits, then print blank?

      I know how to do the Left function to show the 8 digits, but want to depict if the item does not have a valid account ie:

       

      Valid account:  10096 RR SBLWA-1219880071     (available with last 8 digits

      Invalid Account:  34797   (does not have 8 digits available)

        • formula question
          Joe Berry

          There may well be other ways to accomplish this, but here is one:

           

          If(IsNull(Val(Right(FieldName,8))=1),"",Right(FieldName,8))

           

          It checks the eight digits to the right and makes sure that they are numeric.  If they are numeric they are populated in the calculated field; otherwise it is blank.

            • formula question
              adonis _

              Hi Joe

               

              unfortunately it does not do that

               

              it is basically doing the same thing as my Right(,8)

              Sample data below

              please let me know if you have any ideas to get rid of the noise only the first item below appears to be valid, the rest are not and should be blank

               

              09870400

              24 00071

              04 00042

              98 00071

              MARKETS

              MARKETS

              91255

                • formula question
                  Data Kruncher

                  I've got about five minutes before I "go offline" for the day, but try this out:

                   

                  If(Len(RSplit(Account,2," ",1))<8,"",If(IsNull(Val(RSplit(Account,2," ",1))),"",RSplit(Account,2," ",1)))[/CODE]

                    • formula question
                      adonis _

                      Thanks Data for trying

                      don't think that is working as it gives me the following,

                      removing blanks and the whole thing, i need basically to display the last 8 digits, and if they are numeric then print it, and if not, then print blank

                       

                       

                      -000800109870400

                       

                       

                       

                       

                       

                       

                      000WA-1509874801

                      SBLWA-1219880369

                      MGTCBEBEECL

                      SBLWA-1210019535

                      SBLWA-1219880073

                      SBLWA-1219880071

                       

                      000WA-1209874801

                      000GA-6009872354

                       

                      2T9HE-0109876000

                       

                      BSDTUS33

                      //XX629132

                       

                      BSDTUS33

                      BSDTUS33

                      BSDTUS33

                       

                       

                       

                      000WA-2009874801

                       

                       

                      10026827

                        • formula question
                          Joe Berry

                          This first removes any alpha character or space and then tests the length of the remaining field.  If it is not 8 characters long, the field is a blank; otherwise, it is FieldName.

                           

                          If(Len(Strip(FieldName,"abcdefghijklmnopqrstuvwxyz ABCDEFGHIJKLMNOPQRSTUVWXYZ"))<>8,"",FieldName)

                            • formula question
                              Olly Bond

                              Hello Adonis, hello everyone,

                               

                              I think Joe´s approach is definitely right, but in case the data contains rogue lines like:

                               

                              1234-5678

                              123//45678

                              12 345678[/CODE]

                               

                              and the intention is to exclude[/B] these, then we need to rsplit before we strip the field. I´m assuming that the separators are only space, / and -, but if others are used this could be extended.

                               

                              Perhaps it´s easier to split this into three stages - firstly define a calculated field containing the right hand side of the field, broken on the possible separators. If there are no separator characters, then the whole field will be returned:

                               

                              =rsplit(rsplit(rsplit(;2;" ",1);2;"/";1);2;"-";1)

                               

                              Now we can make a new calculated field by removing from the field of all its non-numeric characters:

                               

                              =strip(;strip(;"01234546789"))

                               

                              Finally, we can make which is the account number where this field has exactly eight characters, and is "Invalid" in other cases:

                               

                              =if(len()=8;[stripped];"Invalid")

                               

                              You can then filter out all lines where ="Invalid" easily.

                               

                              Best wishes,

                               

                              Olly

                    • formula question
                      Nick Osdale-Popa

                      First, check that the Right 8 characters have a length of 8. Then, strip out all numbers. If the length is 0, then you have 8 digits:

                      IF(LEN(RIGHT(FIELD,8))=8,IF(LEN(STRIP(RIGHT(Field,8),"0123456789"))=0,RIGHT(FIELD,8),""),"")[/quote]