IF Formula Help

[jbeer]

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

 

[Fluff]

Hi & welcome to MrExcel.
How about

=IF(OR(A1+0=REPT(LEFT(A1),LEN(A1))+0,LEN(A1)<6,LEN(A1)>9),"Invalid","Valid")

 

[jtakw]

Hi,

You say the IDs are to be 6 to 9 characters, but didn't specify requirements for the other/remaining 3 characters.
This will Flag consecutive 6 same number anywhere within the ID as Invalid, whether it's All numeric, or Alpha-numeric, and must be 6 to 9 characters to be Valid:

Book3.xlsx
	A	B
1
2	1111112	Invalid
3	027862	Valid
4	2333333	Invalid
5	122222234	Invalid
6	123333334	Invalid
7	123444444	Invalid
8	132465987	Valid
9	1234	Invalid
10	1234506789	Invalid
11	122222345	Valid
12	ABC111111	Invalid
13	ABCD11111	Valid
Sheet844

Cell Formulas
Range	Formula
B2:B13	B2	=IF(OR(MID(A2,{1,2,3,4},6)=REPT(MID(A2,{1,2,3,4},1),6),LEN(A2)<6,LEN(A2)>9),"Invalid","Valid")

 

[jtakw]

Or, if the IDs Must be All numbers, use C2 formula:

Book3.xlsx
	A	B	C	D
1
2	1111112	Invalid	Invalid	7
3	027862	Valid	Valid	6
4	2333333	Invalid	Invalid	7
5	122222234	Invalid	Invalid	9
6	123333334	Invalid	Invalid	9
7	123444444	Invalid	Invalid	9
8	132465987	Valid	Valid	9
9	1234	Invalid	Invalid	4
10	1234506789	Invalid	Invalid	10
11	122222345	Valid	Valid	9
12	ABC111111	Invalid	Invalid	9
13	ABCD11111	Valid	Invalid	9
Sheet844

Cell Formulas
Range	Formula
B2:B13	B2	=IF(OR(MID(A2,{1,2,3,4},6)=REPT(MID(A2,{1,2,3,4},1),6),LEN(A2)<6,LEN(A2)>9),"Invalid","Valid")
C2:C13	C2	=IF(OR(ISERR(A2+0),MID(A2,{1,2,3,4},6)=REPT(MID(A2,{1,2,3,4},1),6),LEN(A2)<6,LEN(A2)>9),"Invalid","Valid")
D2:D13	D2	=LEN(A2)

 

[jbeer]

Or, if the IDs Must be All numbers, use C2 formula:

Book3.xlsx
	A	B	C	D
1
2	1111112	Invalid	Invalid	7
3	027862	Valid	Valid	6
4	2333333	Invalid	Invalid	7
5	122222234	Invalid	Invalid	9
6	123333334	Invalid	Invalid	9
7	123444444	Invalid	Invalid	9
8	132465987	Valid	Valid	9
9	1234	Invalid	Invalid	4
10	1234506789	Invalid	Invalid	10
11	122222345	Valid	Valid	9
12	ABC111111	Invalid	Invalid	9
13	ABCD11111	Valid	Invalid	9
Sheet844

Cell Formulas
Range	Formula
B2:B13	B2	=IF(OR(MID(A2,{1,2,3,4},6)=REPT(MID(A2,{1,2,3,4},1),6),LEN(A2)<6,LEN(A2)>9),"Invalid","Valid")
C2:C13	C2	=IF(OR(ISERR(A2+0),MID(A2,{1,2,3,4},6)=REPT(MID(A2,{1,2,3,4},1),6),LEN(A2)<6,LEN(A2)>9),"Invalid","Valid")
D2:D13	D2	=LEN(A2)

That is great the formula in C2 I think is going to work best for me...I'll test it at work tomorrow and will revert back if I need further help.

Thanks again