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]

I'll show you what I mean and what I have.

The formula in Column B Must be confirmed with CSE, Anything other than 0 to 9, and Capital A to Z, will be Flagged:

Book3.xlsx
	A	B	C	D
1		0	<Flag Column	Count of erroneous case#
2	12 345	1	Can be 1 or 0	13
3	123 45	1	Or TRUE/FALSE
4	12345a	1
5	123 456	1
6	123456	1
7	123456 7	1
8	123456	1
9	ab1234	1
10	)(&..$	1
11	AB1C23	0
12		0
13	A$BC 789!a	1
14	12345678	1
15	012345	0
16	abcdef	1
17	a2c4e7	1
18	ABCDEF	0
19	A2C4E7	0
20		0
Sheet788

Cell Formulas
Range	Formula
D2	D2	=COUNTIF(B1:B100,1)
B1:B20	B1	=IF(A1="",0,N(OR(LEN(A1)<>6,SUM(--(ISNUMBER(FIND(CHAR(ROW($32:$47)),A1))),--(ISNUMBER(FIND(CHAR(ROW($58:$64)),A1))),--(ISNUMBER(FIND(CHAR(ROW($91:$255)),A1)))))))
Press CTRL+SHIFT+ENTER to enter array formulas.

PS: You do realize you can Hide the Flag Column if you wanted to, and Still be able to use it for the Count and CF/Highlight the erroneous Case #s

 

[AndyTampa]

I do understand that I can hide the column, but when my boss copies everything submitted to her on this form from the submission to the master sheet where she consolidates them, the hidden columns will still paste even if she pastes as text.

I'm trying to understand the formula, but I'm working too, so it will take some time to figure it out. It looks like it's similar to a thought I had this morning, but much more complex than I thought it would be. I was thinking there are only 36 acceptable characters for this column's cells and those are in two ranges of character numbers. I was going to look into counting the characters in the entire range that are in those 36 characters and divide by 6. The answer should match the total number of occupied cells. But then the solution could still be true if some cells have more than 6 characters. It's just a thought I was perusing.

 

[AndyTampa]

I'm toying with a data validation formula I found on the internet that only allows certain letters to be put in the cells.

=ISNUMBER(SUMPRODUCT(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ")))

I've tested it as a formula, but it still allows the lowercase letters. I haven't been able to get it to work in data validation yet. If I could use this AND limit the input to no more or less than 6 characters, it might do the trick.

 

[jtakw]

I tested that formula you have in Post #13 against my sample data in Post #11, and it does not flag correctly.

 

[Rick Rothstein]

Give this a try for your Data Validation formula...

=SUMPRODUCT((LEN(A1)=6)*(CODE(MID(A1,ROW(1:6),1))<91)*((MID(A1,ROW(1:6),1)>="A")*(MID(A1,ROW(1:6),1)<="Z")+ISNUMBER(-MID(A1,ROW(1:6),1))))=6

 

[Tetra201]

See if the following formula works in your data validation:

=AND(LEN(A1)=6,SUM(SIGN(FIND(MID(A1,ROW(A1:A6)-ROW(A1)+1,1),"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ")))=6)

Use SEARCH instead of FIND if you want to allow lower-case letters (caveat: * and ? would be allowed as well).

A word of caution: data validation can be easily fooled by using copy-and-paste.

 

[AndyTampa]

@jtakw, the formula mostly works except it can't differentiate between upper and lowercase characters. Is that what you mean by flags?

@Rick, there appears to be something wrong with the formula. It evaluates to a #VALUE! error.

 

[Rick Rothstein]

When you report an error, it would help if you told us the condition when the error occurred. I'm guessing your error occurred when cell A1 was blank. Try this modified version of the formula instead...

=IF(A1="","",SUMPRODUCT((LEN(A1)=6)*(CODE(MID(A1,ROW(1:6),1))<91)*((MID(A1,ROW(1:6),1)>="A")*(MID(A1,ROW(1:6),1)<="Z")+ISNUMBER(-MID(A1,ROW(1:6),1))))=6)

 

[jtakw]

I'm toying with a data validation formula I found on the internet that only allows certain letters to be put in the cells.

=ISNUMBER(SUMPRODUCT(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ")))

I've tested it as a formula, but it still allows the lowercase letters. I haven't been able to get it to work in data validation yet. If I could use this AND limit the input to no more or less than 6 characters, it might do the trick.

@jtakw, the formula mostly works except it can't differentiate between upper and lowercase characters. Is that what you mean by flags?

I modified that formula, tested it, and it Should work for your Data Validation.
It Won't allow more or less than 6 characters, Anything other than 0 to 9, and/or Capitalized A to Z.

TRUE means it'll be accepted by DV, just check the "Ignore blank" in DV:

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

Cell Formulas
Range	Formula
G1:G20	G1	=AND(LEN(A1)=6,ISNUMBER(SUMPRODUCT(FIND(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"))))
H1:H20	H1	=LEN(A1)

 

[Rick Rothstein]

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