4 Replies Latest reply: May 15, 2014 10:02 AM by CloudenL _ RSS

    Calculated Field V9 Pro

    CloudenL _

      I have a character field that has a customer id number that i need to get rid of leading zeros, but still maintain the id numbers that start with a alpha character.  I've tried to val() and that gets rid of the zeros, but i can't convert back to a character and keep the alpha characters in the id's that don't need any changes.

       

      I hope this makes sense. I can do what i need in excel by using IF(ISERROR(VALUE(A1)),A1,VALUE(A1)), but i don't have an iserror function to use in Monarch.

       

      Please help!!!

        • Calculated Field V9 Pro
          Grant Perkins

          Hi CloudenL and welcome to the forum.

           

          Have you tried the ISALPHA() function? It checks for the first character in a specified string being alpha. See the Help for an example.

           

          So, off the top of my head,

           

          IF(ISALPHA(field)=1, field, str(val(field)))

           

          should be close to what you require assuming the cell needs to be character. If you want the number as a number skip the str() bit BUT that will have other consequences you will need to consider.

           

          HTH.

           

           

          Grant

            • Calculated Field V9 Pro
              CloudenL _

              Thanks,

              That worked, but i found that there are fields where there is alpha characters after numerics as well.  Thos drop off.  Any thing else i can do for those instances.

                • Calculated Field V9 Pro
                  Grant Perkins

                  If the characters are at the END of the string you could use something like ISALPHA(Right(field),1) and then include that as an alternative in your IF statement.

                   

                  If the alphas could be in the middle of the 'number' things get more interesting. If they could be randomly located the challenge becomes quite exciting! 

                   

                  One approach to that would be to use LEN() to get the length of the string and compare that with the length of the result of the of using STRIP() to remove any alpha characters (or numerics I suppose) and if the length stays the same (or if you strip numerics, hits 0) then you know whether to retain it as it is or convert it to a VAL().

                   

                  Which approach you prefer will depend on what sort of issues you may need to contend with. Indeed you can use a combination of these techniques to automatically highlight any records that seem to break any rules you expect to have for data formats within the incoming records. That in itself could be useful as an additional feature for your extract.

                   

                  HTH,

                   

                   

                  Grant