Hi All,
I'm trying to create an IF formula that validates employee id's in a spreadsheet.
The criteria is that the numeric numbers can be between 6 and 9 in length but must not contain all of the same numbers like 111111, 222222, 333333 etc..., so I have the below formula but when I enter 111111 it comes back as Valid so this is picking up the 6 numeric in the final part of the formula.
=IF(OR(A1="000000",A1="111111",A1="222222",A1="333333",A1="444444",A1="555555",A1="666666",A1="777777",A1="888888",A1="999999"),"Invalid",
IF(OR(SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9,0},)))=6,SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9,0},)))=7,SUM(LEN(A1)-LEN(SUBSTITUTE(F8,{1,2,3,4,5,6,7,8,9,0},)))=8),"Valid",
"Invalid"))
Thanks
I'm trying to create an IF formula that validates employee id's in a spreadsheet.
The criteria is that the numeric numbers can be between 6 and 9 in length but must not contain all of the same numbers like 111111, 222222, 333333 etc..., so I have the below formula but when I enter 111111 it comes back as Valid so this is picking up the 6 numeric in the final part of the formula.
=IF(OR(A1="000000",A1="111111",A1="222222",A1="333333",A1="444444",A1="555555",A1="666666",A1="777777",A1="888888",A1="999999"),"Invalid",
IF(OR(SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9,0},)))=6,SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9,0},)))=7,SUM(LEN(A1)-LEN(SUBSTITUTE(F8,{1,2,3,4,5,6,7,8,9,0},)))=8),"Valid",
"Invalid"))
Thanks