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?

 

[Tetra201]

... 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'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?

Try the following formula for data validation:

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

and the following array formula (to be entered using Ctrl+Shift+Enter, not just Enter) for counting all cells with 6 alphanumeric symbols:

=SUMPRODUCT(--(LEN(A1:A1000)=6),--(MMULT(SIGN(IFERROR(FIND(MID(A1:A1000,{1,2,3,4,5,6},1),"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"),0)),{1;1;1;1;1;1})=6))

 

[jtakw]

@JGordon11
and DV won't accept CSE formulas.

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

To clarify, I said CSE formulas (which is different than normal formulas that uses arrays), I've not been able to enter formulas in DV that requires CSE in a regular cell...may be I just don't know how, would you be kind enough to explain.
I enter a CSE formula in the DV custom, formula box, then CSE, but it would not perform the array.

Or, may be it's because I'm using 2016...

 

[Tetra201]

As an example,
Cell formula: if you have =A1=SUM(ROW(A$1:A1)) in cell B1, it needs to be entered with a CSE to work correctly when you drag-copy it down to, say, cell B5.
DV formula: if you apply the same =A1=SUM(ROW(A$1:A1)) as a Data Validation formula for range A1:A5, it works right away allowing you to enter only 1 in A1, 3 in A2, 6 in A3, etc.
This has been tested to work in Excel 2010 (Win), 2011 (Mac), and 365 (Win).

 

[jtakw]

Thank you, from the bottom of my heart, you're among the Top greatest contributors here, I'm sure you have helped me numerous of times before.
But I think that formula would have worked whether or not it was entered CSE in DV, but I see the point.
This would work for both: =A1=SUMPRODUCT((ROW(A$1:A1)))

 

[jtakw]

I'm more interested in modifying my formula in Post #19 without the volatile INDIRECT function for DV:

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

 

[Rick Rothstein]

Here is a non-volatile formula (shorter than the one I posted in Message #18 that was also non-volatile) that appears to work...

=IF(LEN(A1)=6,(SUMPRODUCT((ABS(CODE(MID(LEFT(A1,6),ROW(1:6),1))-69)<=21)*(ABS(CODE(MID(LEFT(A1,6),ROW(1:6),1))-61)>3))=6))

 

[Tetra201]

I'm more interested in modifying my formula in Post #19 without the volatile INDIRECT function for DV...

A non-volatile equivalent for ROW(INDIRECT("1:"&LEN(A1))) would be, for example,

ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)))

 

[Tetra201]

@ Rick:
IMO, your formulas need to be modified to prevent "reference drift." Here is why:
When your formulas are used in DV for a range in column A, they only work for cell A1.
In cell A2, the array-producing reference becomes ROW(2:7).

 

[Rick Rothstein]

@ Rick:
IMO, your formulas need to be modified to prevent "reference drift."

Of course!!! I used the "Mad" smilie but the old "Bang Head" smilie would have been more appropriate. Thanks for pointing this out. Here is the corrected formula that I should have posted originally...

=IF(LEN(A1)=6,(SUMPRODUCT((ABS(CODE(MID(LEFT(A1,6),ROW($1:$6),1))-69)<=21)*(ABS(CODE(MID(LEFT(A1,6),ROW($1:$6),1))-61)>3))=6))

 

[Tetra201]

Rick, there is yet another issue: if you insert or delete a row, the absolute reference still drifts/expands/shrinks.
Looks like I need to modify my DV formula as well and use the following array-producing function: ROW(INDEX(A:A,1):INDEX(A:A,6)).
Upd: wrong idea -- this does not work in DV.