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?
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,853
Office Version
  1. 2010
Platform
  1. Windows
Hmm, I wonder what I am doing wrong then? When I tested it, it allowed me to type anything in the cell. I have to look at it again.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,853
Office Version
  1. 2010
Platform
  1. Windows
Hmm, I wonder what I am doing wrong then? When I tested it, it allowed me to type anything in the cell. I have to look at it again.
Never mind... stupid mistake on my part.:mad: I applied it to cell A2 without changing the formula's cell reference from A1 to A2 {this is a time when I miss the old "bang head" smiley}.
 

jtakw

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

ADVERTISEMENT

Take a look at Post #16 for a non-volatile formula ;)

I did test it Tetra, this is what happened:

Cell Formulas
RangeFormula
H1:H20H1=LEN(A1)
L1:L20L1=AND(LEN(A1)=6,SUM(SIGN(FIND(MID(A1,ROW(A$1:A$6)-ROW(A$1)+1,1),"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ")))=6)
M1:M20M1=AND(LEN(A1)=6,SUM(SIGN(FIND(MID(A1,ROW(A$1:A$6)-ROW(A$1)+1,1),"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ")))=6)
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Last edited:

JGordon11

Board Regular
Joined
Jan 18, 2021
Messages
65
Office Version
  1. 365
Platform
  1. Windows
BinomialPermutations.xlsm
AB
112 3450
2123 450
312345a0
4123 4560
51234561
6123456 70
7123A451
8ab12340
9)(&..$0
10AB1C231
110
12A$BC 789!a0
13123456780
14123450
15abcdef0
16a2c4e70
17ABCDEF1
18A2C4E71
19ABCDEFG0
Sheet2
Cell Formulas
RangeFormula
B1:B19B1=NOT(ISERROR(SUM(FIND(MID(A1,ROW($1:$6),1),"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"))))*(LEN(A1)=6)
Press CTRL+SHIFT+ENTER to enter array formulas.
 

jtakw

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

ADVERTISEMENT

@JGordon11, I did something similar back in Post #11, but OP does not want a helper column to Flag and for use to Count, and use for CF.

and DV won't accept CSE formulas.
 

JGordon11

Board Regular
Joined
Jan 18, 2021
Messages
65
Office Version
  1. 365
Platform
  1. Windows
@JGordon11, I did something similar back in Post #11, but OP does not want a helper column to Flag and for use to Count, and use for CF.

and DV won't accept CSE formulas.
Thanks - didn't realize DV doesn't do array formulas. Although this helper column on a hidden sheet and referenced by DV would solve the problem (DV, count, and CF) without the users having to see a strange (or hidden) column of 1s and 0s.
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,847
Office Version
  1. 2016
Platform
  1. Windows
I agree, I explained that in Post #11 to OP, but it doesn't seem like an option he's considering.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,997
Messages
5,622,083
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