3 Replies Latest reply on Nov 11, 2013 1:08 PM by Jessica Lee

    Creating IF function with string data

    Jessica Lee

      Hi all,

       

      I am trying to write IF function which will validate string data.

       

      TypeNumberValid or Invalid
      1123
      2123456789
      387512389
      4846952
      50
      699999999
      712345678

       

      This is the sample of data that I have.

      What I want to do is add IF function to compute values in "Valid or Invalid" column.

      The rules for validation is this:

      • Number should be 8 digits
      • Number shouldn't be repetitive or sequential ex) 9999999, 123456789, 1234567, etc.

       

      What I did was switch the data type from String to Number then write the function below.

       

      If [Number] < 10000000 or [Number] > 99999999 THEN 'Invalid' ELSE 'Valid' END

       

      This function could help me identifying any numbers that has less or more than 8 digits, but doesn't help me identifying any repetitive or sequential numbers. So, type 6 and 7 are still marked as valid when it should be marked as invalid.

       

      Can anyone help me identifying repetitive or sequential number to validate those numbers?

       

      Thank you !

        • 1. Re: Creating IF function with string data
          Dallin Crump

          Jessica,

           

          Here is what I came up with:

           

          IF

           

          [Number] < 10000000

           

          OR [Number] > 99999999

           

          OR (MID(STR([Number]),1,1) = MID(STR([Number]),2,1)

                  AND MID(STR([Number]),2,1) = MID(STR([Number]),3,1)

                  AND MID(STR([Number]),3,1) = MID(STR([Number]),4,1)

                  AND MID(STR([Number]),4,1) = MID(STR([Number]),5,1)

                  AND MID(STR([Number]),5,1) = MID(STR([Number]),6,1)

                  AND MID(STR([Number]),6,1) = MID(STR([Number]),7,1)

                  AND MID(STR([Number]),7,1) = MID(STR([Number]),8,1))

           

          OR (INT(MID(STR([Number]),1,1)) + 1 = INT(MID(STR([Number]),2,1))

                  AND INT(MID(STR([Number]),2,1)) + 1 = INT(MID(STR([Number]),3,1))

                  AND INT(MID(STR([Number]),3,1)) + 1 = INT(MID(STR([Number]),4,1))

                  AND INT(MID(STR([Number]),4,1)) + 1 = INT(MID(STR([Number]),5,1))

                  AND INT(MID(STR([Number]),5,1)) + 1 = INT(MID(STR([Number]),6,1))

                  AND INT(MID(STR([Number]),6,1)) + 1 = INT(MID(STR([Number]),7,1))

                  AND INT(MID(STR([Number]),7,1)) + 1 = INT(MID(STR([Number]),8,1)))

           

          THEN 'Invalid'

           

          ELSE 'Valid' END

           

          Probably not the most elegant or efficient solution, but it seems to work. See the attached workbook.

           

          Best,

           

          Dallin

          1 of 1 people found this helpful
          • 2. Re: Creating IF function with string data
            Michel Caissie

            Jessica,

             

            Considering the validation rules, you can do this without changing the data type from String to Number.

             

            First using the LEN  function , you can easily get rid of all Numbers not having 8 characters. Next since there is not much invalid possibilities on the remaining Numbers,  you can use a CASE to keep the valid ones.

             

            See the attached

             

            Michel

            • 3. Re: Creating IF function with string data
              Jessica Lee

              This works pretty well! Thanks!