Count cells that contain 6 characters or numbers but no spaces

AndyTampa

Board Regular
Joined
Aug 14, 2011
Messages
184
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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?
 
... 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:
Excel Formula:
=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:
Excel Formula:
=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))
 
Upvote 0

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
@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...
 
Upvote 0
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).
 
Upvote 0
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)))
 
Upvote 0
I'm more interested in modifying my formula in Post #19 without the volatile INDIRECT function for DV:

Excel Formula:
=AND(LEN(A1)=6,ISNUMBER(SUMPRODUCT(FIND(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"))))
 
Upvote 0
Here is a non-volatile formula (shorter than the one I posted in Message #18 that was also non-volatile) that appears to work...
Excel Formula:
=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))
 
Upvote 0
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,
Excel Formula:
ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)))
 
Upvote 0
@ 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).
 
Upvote 0
@ Rick:
IMO, your formulas need to be modified to prevent "reference drift."
Of course!!! :mad: 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...
Excel Formula:
=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))
 
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top