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

Creating IF function with string data

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

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

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

This works pretty well! Thanks!