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:




          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



              24 00071

              04 00042

              98 00071




                • 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



































                        • 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:




                              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:




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




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


                              Best wishes,



                    • 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: