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


      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



          Here is what I came up with:




          [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.





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



            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



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

              This works pretty well! Thanks!