Count cells that contain 6 characters or numbers but no spaces

AndyTampa

Board Regular
Joined
Aug 14, 2011
Messages
114
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

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,643
... 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))
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,845
Office Version
  1. 2016
Platform
  1. Windows
@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

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,643
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

Well-known Member
Joined
Jun 29, 2014
Messages
5,845
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Jun 29, 2014
Messages
5,845
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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"))))
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,853
Office Version
  1. 2010
Platform
  1. Windows
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))
 

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,643

ADVERTISEMENT

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)))
 

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,643
@ 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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,853
Office Version
  1. 2010
Platform
  1. Windows
@ 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))
 

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,643
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:

Watch MrExcel Video

Forum statistics

Threads
1,126,996
Messages
5,622,074
Members
415,875
Latest member
Tarali

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
Top