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?
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,858
Office Version
  1. 2010
Platform
  1. Windows
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.
It is hard to know if we should go that "extra mile" and protect against row insertions or not. Anyway, assuming we should, then it Looks like it is back to the INDIRECT function then (your update note is right, the INDEX referencing does not work in Data Validation)....
Excel Formula:
=IF(LEN(A1)=6,(SUMPRODUCT((ABS(CODE(MID(LEFT(A1,6),ROW(INDIRECT("1:6")),1))-69)<=21)*(ABS(CODE(MID(LEFT(A1,6),ROW(INDIRECT("1:6")),1))-61)>3))=6))

Side note: When I tested to make sure Data Validation would not accept the INDEX references, the note telling me that listed a few taboo items and one of them was the new LAMBDA function. I am using XL2016 right now which does not have, and as far as I know will never have, the LAMBDA function available to it... I found it strange that the warning message would mention it.
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

AndyTampa

Board Regular
Joined
Aug 14, 2011
Messages
114
Wow. You guys are really providing lots of choices that are way beyond my skill level. I can't keep up. Priorities at work have kept me from working on this for now, so I don't have a result yet, but I will let you all know what works for me. I'm trying to keep this as simple as possible for my leadership, but I still have to be able to explain it. I'll be researching how each formula works but it won't be quick. :(

Thank you to everyone.
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,852
Office Version
  1. 2016
Platform
  1. Windows
You're welcome AndyTampa,

Make sure you try my formula in Post #19 for DV, cause I've tested it against my sample data to work 100%.
 

AndyTampa

Board Regular
Joined
Aug 14, 2011
Messages
114
My lord how many options you've given me. I've gone through the conversation and copied out some of your suggestions to test the formulas. I haven't yet tested them for Data Validation, but this is what I'm looking at so far.

OSR Formulas Template.xlsx
ABCDEFG
1#1 Post 15#2 Post 16#3 Post 18#4 Post 19#5 Post 39
2Rick (fixed drift)TetraRick (fixed drift)jtakwRick
3EMPTY#VALUE!FALSE FALSEFALSE
4#$,.(&SPCL CHARFALSE#VALUE!FALSEFALSEFALSE
5123456VALIDTRUEFALSETRUETRUETRUE
6 12345LEAD SPFALSE#VALUE!FALSEFALSEFALSE
7123 456MID SPFALSEFALSEFALSEFALSEFALSE
8123456 TRAIL SPFALSEFALSEFALSEFALSEFALSE
912345677 DIGITFALSEFALSEFALSEFALSEFALSE
10123(56PUNCTFALSEFALSEFALSEFALSEFALSE
11abcdefLOWER CASEFALSE#VALUE!FALSEFALSEFALSE
12ABCDEFVALIDTRUEFALSETRUETRUETRUE
13AbCdEfLOWER CASEFALSEFALSEFALSEFALSEFALSE
14 ABCDELEAD SPFALSE#VALUE!FALSEFALSEFALSE
15ABC DEFMID SPFALSEFALSEFALSEFALSEFALSE
16ABCDEF TRAIL SPFALSEFALSEFALSEFALSEFALSE
17ABCDEFG7 LETTERFALSEFALSEFALSEFALSEFALSE
18ABCD#FSPCL CHARFALSEFALSEFALSEFALSEFALSE
19A1B2C3VALIDTRUEFALSETRUETRUETRUE
20a1b2c3LOWER CASEFALSE#VALUE!FALSEFALSEFALSE
21#VALUE!FALSE FALSEFALSE
22#VALUE!FALSE FALSEFALSE
Sheet1 (2)
Cell Formulas
RangeFormula
C3:C22C3=SUMPRODUCT((LEN(A3)=6)*(CODE(MID(A3,ROW($1:$6),1))<91)*((MID(A3,ROW($1:$6),1)>="A")*(MID(A3,ROW($1:$6),1)<="Z")+ISNUMBER(-MID(A3,ROW($1:$6),1))))=6
D3:D22D3=AND(LEN(A3)=6,SUM(SIGN(FIND(MID(A3,ROW(A3:A8)-ROW(A3)+1,1),"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ")))=6)
E3:E22E3=IF(A3="","",SUMPRODUCT((LEN(A3)=6)*(CODE(MID(A3,ROW($1:$6),1))<91)*((MID(A3,ROW($1:$6),1)>="A")*(MID(A3,ROW($1:$6),1)<="Z")+ISNUMBER(-MID(A3,ROW($1:$6),1))))=6)
F3:F22F3=AND(LEN(A3)=6,ISNUMBER(SUMPRODUCT(FIND(MID(A3,ROW(INDIRECT("1:"&LEN(A3))),1),"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"))))
G3:G22G3=IF(LEN(A3)=6,(SUMPRODUCT((ABS(CODE(MID(LEFT(A3,6),ROW($1:$6),1))-69)<=21)*(ABS(CODE(MID(LEFT(A3,6),ROW($1:$6),1))-61)>3))=6))


Correct results are in green. I had to edit a couple of Rick's formulas for drift as suggested in one of the posts, which got them to work better.

These are the formulas I tested:

#1Rick Rothstein #15 for DV
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
Non-driftSUMPRODUCT((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
#2Tetra #16 for DV (Non-volatile)
AND(LEN(A1)=6,SUM(SIGN(FIND(MID(A1,ROW(A1:A6)-ROW(A1)+1,1),"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ")))=6)
#3Rick Rothstein #18
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)
Non-driftIF(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)
#4jtakw #19 for DV
AND(LEN(A1)=6,ISNUMBER(SUMPRODUCT(FIND(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"))))
#5Rick Rothstein #39 for DV (Non-volatile)
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))

I still don't know how any of them work and I don't know the difference between Volatile and Non-volatile. I am now aware that pasting the numbers will defeat DV. This brings up a few questions.

1) How will the "volatile" / "non-volatile" issue affect DV and my sheet in general?
2) Can I prevent pasting on a protected worksheet?
3) If I can prevent pasting, how will that affect my supervisor copying from the sheet and pasting to a master sheet?

I'm going to test these for validation next and see if I can get them to work. I tried pasting into DV, and that didn't work. :unsure:
 

AndyTampa

Board Regular
Joined
Aug 14, 2011
Messages
114
I can't figure out what I'm doing wrong, but all five seem to work inside of Data Validation including Tetra's formula that is also non-volatile. Somehow, the formula isn't working in the table. Is that normal?
 

AndyTampa

Board Regular
Joined
Aug 14, 2011
Messages
114
I'm back after being pulled for a project and was hoping to see answers to my questions. I still don't understand the formulas. They are super-advanced, especially where turning characters into numbers and using arrays. My head hurts.

Can I prevent pasting into the cells?
What does volatile/non-volatile mean?

I've toyed with the idea of using Conditional formatting instead of data validation which places the responsibility of using the right characters on the user, but that can also be defeated by pasting.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,119
Messages
5,622,837
Members
415,934
Latest member
adstocking

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