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?
 
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.
 
Upvote 0

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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.
 
Upvote 0
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%.
 
Upvote 0
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:
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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. :cry:

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
ABCDEFGHI
1DO NOT SUBMIT DIRECTLY FOR PROCESSING. COPY/PASTE AS TEXT TO MASTER SHEET.
2TOTAL-B2YOUR NAME00000005.000000001B2202105271
3CASEDEP DATEAMOUNTADJPERIOD TO APPLYMSTR CASE
4ABC1232021-03-25-00000005.00V22021-01-01
Sheet1
Cell Formulas
RangeFormula
C2C2=SUM(C4:C199963)
E2E2=I2+VALUE(0)
G2G2=TODAY()
I2I2=FIXED(COUNTIF(A4:A199840,"> "),0)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D4:D1048576Expression=OR(AND(C4<0,ISNUMBER(VALUE(LEFT(D4,1)))),AND(C4>0,NOT(ISNUMBER(VALUE(LEFT(D4,1))))))textNO
C4:C1048576Expression=OR(AND(C4<0,ISNUMBER(VALUE(LEFT(D4,1)))),AND(C4>0,NOT(ISNUMBER(VALUE(LEFT(D4,1))))))textNO
Cells with Data Validation
CellAllowCriteria
A4Custom=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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

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