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?

 

[Rick Rothstein]

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

=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.

 

[AndyTampa]

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]

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]

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
	A	B	C	D	E	F	G
1			#1 Post 15	#2 Post 16	#3 Post 18	#4 Post 19	#5 Post 39
2			Rick (fixed drift)	Tetra	Rick (fixed drift)	jtakw	Rick
3		EMPTY	#VALUE!	FALSE		FALSE	FALSE
4	#$,.(&	SPCL CHAR	FALSE	#VALUE!	FALSE	FALSE	FALSE
5	123456	VALID	TRUE	FALSE	TRUE	TRUE	TRUE
6	12345	LEAD SP	FALSE	#VALUE!	FALSE	FALSE	FALSE
7	123 456	MID SP	FALSE	FALSE	FALSE	FALSE	FALSE
8	123456	TRAIL SP	FALSE	FALSE	FALSE	FALSE	FALSE
9	1234567	7 DIGIT	FALSE	FALSE	FALSE	FALSE	FALSE
10	123(56	PUNCT	FALSE	FALSE	FALSE	FALSE	FALSE
11	abcdef	LOWER CASE	FALSE	#VALUE!	FALSE	FALSE	FALSE
12	ABCDEF	VALID	TRUE	FALSE	TRUE	TRUE	TRUE
13	AbCdEf	LOWER CASE	FALSE	FALSE	FALSE	FALSE	FALSE
14	ABCDE	LEAD SP	FALSE	#VALUE!	FALSE	FALSE	FALSE
15	ABC DEF	MID SP	FALSE	FALSE	FALSE	FALSE	FALSE
16	ABCDEF	TRAIL SP	FALSE	FALSE	FALSE	FALSE	FALSE
17	ABCDEFG	7 LETTER	FALSE	FALSE	FALSE	FALSE	FALSE
18	ABCD#F	SPCL CHAR	FALSE	FALSE	FALSE	FALSE	FALSE
19	A1B2C3	VALID	TRUE	FALSE	TRUE	TRUE	TRUE
20	a1b2c3	LOWER CASE	FALSE	#VALUE!	FALSE	FALSE	FALSE
21			#VALUE!	FALSE		FALSE	FALSE
22			#VALUE!	FALSE		FALSE	FALSE
Sheet1 (2)

Cell Formulas
Range	Formula
C3:C22	C3	=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:D22	D3	=AND(LEN(A3)=6,SUM(SIGN(FIND(MID(A3,ROW(A3:A8)-ROW(A3)+1,1),"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ")))=6)
E3:E22	E3	=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:F22	F3	=AND(LEN(A3)=6,ISNUMBER(SUMPRODUCT(FIND(MID(A3,ROW(INDIRECT("1:"&LEN(A3))),1),"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"))))
G3:G22	G3	=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:

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

 

[AndyTampa]

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]

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.

 

[AndyTampa]

I'm back again and there is a short window for me to continue on this template. I had hoped to see some answers to my questions but it feels more like everyone gave up on me at the same time.

I'm looking at the template again and I've reread the entire thread and I think a VBA solution may be my best bet. It will allow the user to input lowercase letters, copy/paste, and insert/delete rows. I'll just add a VALIDATE button that checks the entire spreadsheet then reformats all the cells to be acceptable to IT and flags invalid entries. This is what I have so far.

OSR Formulas Template.xlsx
	A	B	C	D	E	F	G	H	I
1	DO NOT SUBMIT DIRECTLY FOR PROCESSING. COPY/PASTE AS TEXT TO MASTER SHEET.
2	TOTAL-B2	YOUR NAME	00000005.00		0000001	B2	20210527		1
3	CASE	DEP DATE	AMOUNT	ADJ	PERIOD TO APPLY	MSTR CASE
4	ABC123	2021-03-25	-00000005.00	V2	2021-01-01
Sheet1

Cell Formulas
Range	Formula
C2	C2	=SUM(C4:C199963)
E2	E2	=I2+VALUE(0)
G2	G2	=TODAY()
I2	I2	=FIXED(COUNTIF(A4:A199840,"> "),0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D4:D1048576	Expression	=OR(AND(C4<0,ISNUMBER(VALUE(LEFT(D4,1)))),AND(C4>0,NOT(ISNUMBER(VALUE(LEFT(D4,1))))))	text	NO
C4:C1048576	Expression	=OR(AND(C4<0,ISNUMBER(VALUE(LEFT(D4,1)))),AND(C4>0,NOT(ISNUMBER(VALUE(LEFT(D4,1))))))	text	NO

Cells with Data Validation
Cell	Allow	Criteria
A4	Custom	=IF(A4="","",SUMPRODUCT((LEN(A4)=6)*(CODE(MID(A4,ROW($1:$5),1))<91)*((MID(A4,ROW($1:$5),1)>="A")*(MID(A4,ROW($1:$5),1)<="Z")+ISNUMBER(-MID(A4,ROW($1:$5),1))))=6)

The first three rows are to be frozen. The only thing on these rows editable by a user will be "YOUR NAME". IT is particular about the formatting of each piece of data.

1) All characters are to be in uppercase letters.
2) The format for all the dates in the table (except G2) is yyyy-mm-dd.
3) All money is 8 digits before the decimal and 2 digits after it.
4) Everything is left justified.

Column A is what we have been discussing and the idea of drift and volatility has me concerned when a macro can just run down and test each cell without worrying about those things. (In my example, I used Rick's formula in Post #18, but I edited it for drift as suggested in another post. I also had to change the 6s to 5s to get it to work, but I don't remember why.) This week, I was told we needed to add Column F which will also house a case number with the same requirements, but it is the parent of the case in Column A. These two columns cannot be the same.

You can see that I have some conditional formatting in columns C & D, but as I've found out, Conditional Formatting can be blown out by Copy/Paste too. This formatting tests the ADJ code to the actual amount. We have a table of codes and with only 4 exceptions, a positive adjustment has a code beginning with a digit. I've never used the 4 exceptions and don't foresee that as an issue for manual requests.

Ideally, I'd like the VBA macro to:
1) Convert all data to uppercase letters
2) Left justify every cell containing data
3) Format all dates in columns B & E to yyyy-mm-dd
4) Format all money in column C to 00000000.00
5) Line by line verify:
. a) No blank cells between Column A and Column F, inclusive - Highlight the blank cells
. b) Case numbers in columns A & F are 6 alphanumeric characters without spaces or special characters - Highlight cells with errors
. c) Column A and Column F are not equal - Highlight cells with errors
. d) All dates in column E are on the first of the month - Highlight cells with errors. Do not correct. Let the user check his/her error.
. e) For all amounts in Column C below Row 3 that are not negative numbers, the code in Column D must begin with a digit - Highlight both cells as errors if their 'polarity', for lack of a better word, doesn't match.
6) This macro will be triggered by a "VALIDATE" button. It would be spectacular if the button text could change to "VALID" or "INVALID". Otherwise a cell could show "VALID" or "INVALID" if errors are found.

Can VBA check the status of the spreadsheet to know if something was changed since its last save? I'm wondering if this can be used to have the VALIDATE button change to "VALIDATE" once something has been changed.

Am I asking too much?

I can record the majority of the formatting functions, but there has to be an easy code to use instead of keystrokes. I'm somewhat lost when defining things and stepping the code using WHILE statement (or something like it). I know that letting users do what they want and then showing them where the errors are would be easier for everyone using it including my supervisors.

 

[AndyTampa]

Why did you guys give up on me?

I've got all the formatting part of the macro and I'm working on the line-by-line check. I'm trying to figure out how to put this into VBA form.

I need to check columns A & G for the formula's you've given me. I tried using them as is (if cell = formula) but that failed.
Then I need to somehow check a column of codes that correspond to credits and debits to determine if the amounts in the adjacent column have been entered correctly as positive or negative.
There's more I need to work on after all the rows are validated, but I'm going to scour the internet before I ask about that. Those questions are not formula related.