Count cells that contain 6 characters or numbers but no spaces

AndyTampa

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

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
I'll show you what I mean and what I have.

The formula in Column B Must be confirmed with CSE, Anything other than 0 to 9, and Capital A to Z, will be Flagged:

Book3.xlsx
ABCD
10<Flag ColumnCount of erroneous case#
212 3451Can be 1 or 013
3123 451Or TRUE/FALSE
412345a1
5123 4561
61234561
7123456 71
81234561
9ab12341
10)(&..$1
11AB1C230
120
13A$BC 789!a1
14123456781
150123450
16abcdef1
17a2c4e71
18ABCDEF0
19A2C4E70
200
Sheet788
Cell Formulas
RangeFormula
D2D2=COUNTIF(B1:B100,1)
B1:B20B1=IF(A1="",0,N(OR(LEN(A1)<>6,SUM(--(ISNUMBER(FIND(CHAR(ROW($32:$47)),A1))),--(ISNUMBER(FIND(CHAR(ROW($58:$64)),A1))),--(ISNUMBER(FIND(CHAR(ROW($91:$255)),A1)))))))
Press CTRL+SHIFT+ENTER to enter array formulas.


PS: You do realize you can Hide the Flag Column if you wanted to, and Still be able to use it for the Count and CF/Highlight the erroneous Case #s
 
Last edited:

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

AndyTampa

Board Regular
Joined
Aug 14, 2011
Messages
122
I do understand that I can hide the column, but when my boss copies everything submitted to her on this form from the submission to the master sheet where she consolidates them, the hidden columns will still paste even if she pastes as text.

I'm trying to understand the formula, but I'm working too, so it will take some time to figure it out. It looks like it's similar to a thought I had this morning, but much more complex than I thought it would be. I was thinking there are only 36 acceptable characters for this column's cells and those are in two ranges of character numbers. I was going to look into counting the characters in the entire range that are in those 36 characters and divide by 6. The answer should match the total number of occupied cells. But then the solution could still be true if some cells have more than 6 characters. It's just a thought I was perusing.
 

AndyTampa

Board Regular
Joined
Aug 14, 2011
Messages
122
I'm toying with a data validation formula I found on the internet that only allows certain letters to be put in the cells.

=ISNUMBER(SUMPRODUCT(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ")))

I've tested it as a formula, but it still allows the lowercase letters. I haven't been able to get it to work in data validation yet. If I could use this AND limit the input to no more or less than 6 characters, it might do the trick.
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
I tested that formula you have in Post #13 against my sample data in Post #11, and it does not flag correctly.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,975
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Give this a try for your Data Validation formula...
Excel Formula:
=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
 

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,643
See if the following formula works in your data validation:
Excel Formula:
=AND(LEN(A1)=6,SUM(SIGN(FIND(MID(A1,ROW(A1:A6)-ROW(A1)+1,1),"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ")))=6)
Use SEARCH instead of FIND if you want to allow lower-case letters (caveat: * and ? would be allowed as well).

A word of caution: data validation can be easily fooled by using copy-and-paste.
 
Last edited:

AndyTampa

Board Regular
Joined
Aug 14, 2011
Messages
122

ADVERTISEMENT

@jtakw, the formula mostly works except it can't differentiate between upper and lowercase characters. Is that what you mean by flags?

@Rick, there appears to be something wrong with the formula. It evaluates to a #VALUE! error.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,975
Office Version
  1. 2016
Platform
  1. Windows
When you report an error, it would help if you told us the condition when the error occurred. I'm guessing your error occurred when cell A1 was blank. Try this modified version of the formula instead...
Excel Formula:
=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)
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
I'm toying with a data validation formula I found on the internet that only allows certain letters to be put in the cells.

=ISNUMBER(SUMPRODUCT(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ")))

I've tested it as a formula, but it still allows the lowercase letters. I haven't been able to get it to work in data validation yet. If I could use this AND limit the input to no more or less than 6 characters, it might do the trick.

@jtakw, the formula mostly works except it can't differentiate between upper and lowercase characters. Is that what you mean by flags?

I modified that formula, tested it, and it Should work for your Data Validation.
It Won't allow more or less than 6 characters, Anything other than 0 to 9, and/or Capitalized A to Z.

TRUE means it'll be accepted by DV, just check the "Ignore blank" in DV:

Book3.xlsx
AGH
1FALSE0
212 345FALSE6
3123 45FALSE6
412345aFALSE6
5123 456FALSE7
6123456FALSE7
7123456 7FALSE8
8123456FALSE7
9ab1234FALSE6
10)(&..$FALSE6
11AB1C23TRUE6
12FALSE0
13A$BC 789!aFALSE10
1412345678FALSE8
15012345TRUE6
16abcdefFALSE6
17a2c4e7FALSE6
18ABCDEFTRUE6
19A2C4E7TRUE6
20FALSE0
Sheet788
Cell Formulas
RangeFormula
G1:G20G1=AND(LEN(A1)=6,ISNUMBER(SUMPRODUCT(FIND(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"))))
H1:H20H1=LEN(A1)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,569
Messages
5,637,106
Members
416,957
Latest member
Brovashift

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