Count cells that contain 6 characters or numbers but no spaces

[AndyTampa]

I'm trying to create a template for work that will idiot-proof multiple people using it. We need to submit manual changes to IT in a particular format and most people are having trouble following the format.

The first column has the list of alphanumeric case numbers and I'm trying to force them into using correct case numbers. Every case is 6 characters long. I suppose I can use Data Validation to limit the number of characters, but I can't figure out a formula that both limits the number characters to 6 and prevents spaces in those 6 characters.

I've also tried to create an error checking formula that counts all cells containing 6 characters but no spaces for comparison to the total number of cells. I've tried SUMPRODUCT(LEN(SUBSTITUTE(x:x," ",""))) to count total characters and divide by 6 to match to number of non-empty cells, but this fails if, for example, one cell has 9 characters and another has 3.

I've used Conditional Formatting to light up the correct cells with a background color, but that's not truly preventive.

I'd love to have a Data Validation solution with the error-checking, but the error-checking is more important for me to get this done. Any ideas?

 

[jtakw]

@jtakw... did you get that formula to work when used in Data Validation?

Just tested it as custom DV formula on my test data as shown in my sample, works accordingly.

 

[Rick Rothstein]

Hmm, I wonder what I am doing wrong then? When I tested it, it allowed me to type anything in the cell. I have to look at it again.

 

[Rick Rothstein]

Hmm, I wonder what I am doing wrong then? When I tested it, it allowed me to type anything in the cell. I have to look at it again.

Never mind... stupid mistake on my part. I applied it to cell A2 without changing the formula's cell reference from A1 to A2 {this is a time when I miss the old "bang head" smiley}.

 

[Tetra201]

I modified that formula...

Take a look at Post #16 for a non-volatile formula

 

[jtakw]

Take a look at Post #16 for a non-volatile formula

I did test it Tetra, this is what happened:

Book3.xlsx
	A							H				L	M
1								0				FALSE	FALSE
2	12 345							6				FALSE	#VALUE!
3	123 45							6				FALSE	#VALUE!
4	12345a							6				FALSE	#VALUE!
5	123 456							7				FALSE	#VALUE!
6	123456							7				FALSE	FALSE
7	123456 7							8				FALSE	FALSE
8	123456							7				#VALUE!	#VALUE!
9	ab1234							6				#VALUE!	#VALUE!
10	)(&..$							6				#VALUE!	#VALUE!
11	AB1C23							6				FALSE	TRUE
12								0				FALSE	FALSE
13	A$BC 789!a							10				FALSE	#VALUE!
14	12345678							8				FALSE	FALSE
15	012345							6				FALSE	TRUE
16	abcdef							6				#VALUE!	#VALUE!
17	a2c4e7							6				#VALUE!	#VALUE!
18	ABCDEF							6				FALSE	TRUE
19	A2C4E7							6				FALSE	TRUE
20								0				FALSE	FALSE
Sheet788

Cell Formulas
Range	Formula
H1:H20	H1	=LEN(A1)
L1:L20	L1	=AND(LEN(A1)=6,SUM(SIGN(FIND(MID(A1,ROW(A$1:A$6)-ROW(A$1)+1,1),"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ")))=6)
M1:M20	M1	=AND(LEN(A1)=6,SUM(SIGN(FIND(MID(A1,ROW(A$1:A$6)-ROW(A$1)+1,1),"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ")))=6)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[JGordon11]

BinomialPermutations.xlsm
	A	B
1	12 345	0
2	123 45	0
3	12345a	0
4	123 456	0
5	123456	1
6	123456 7	0
7	123A45	1
8	ab1234	0
9	)(&..$	0
10	AB1C23	1
11		0
12	A$BC 789!a	0
13	12345678	0
14	12345	0
15	abcdef	0
16	a2c4e7	0
17	ABCDEF	1
18	A2C4E7	1
19	ABCDEFG	0
Sheet2

Cell Formulas
Range	Formula
B1:B19	B1	=NOT(ISERROR(SUM(FIND(MID(A1,ROW($1:$6),1),"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"))))*(LEN(A1)=6)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[jtakw]

@JGordon11, I did something similar back in Post #11, but OP does not want a helper column to Flag and for use to Count, and use for CF.

and DV won't accept CSE formulas.

 

[JGordon11]

@JGordon11, I did something similar back in Post #11, but OP does not want a helper column to Flag and for use to Count, and use for CF.

and DV won't accept CSE formulas.

Thanks - didn't realize DV doesn't do array formulas. Although this helper column on a hidden sheet and referenced by DV would solve the problem (DV, count, and CF) without the users having to see a strange (or hidden) column of 1s and 0s.

 

[jtakw]

I agree, I explained that in Post #11 to OP, but it doesn't seem like an option he's considering.

 

[Tetra201]

... DV won't accept CSE formulas.

@ jtakw: DV does accept array formulas. Please do not misinform people