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?

 

[jtakw]

Hi,

For DV, you can use this formula:

=LEN(SUBSTITUTE(A2," ",""))=6

For error checking, you can use this formula to Count how many cells within range is Less than 6 characters:

=SUMPRODUCT((A1:A10<>"")*(LEN(SUBSTITUTE(A1:A10," ",""))<6))

Or, this to Count how many cells within range is Exactly 6 characters:

=SUMPRODUCT((A1:A10<>"")*(LEN(SUBSTITUTE(A1:A10," ",""))=6))

 

[gigidica]

Also, if you want to bulletproof the insertion, you can create a textbox form and in the properties of the textbox (Alt+F11) , under the Behavior you can select the length allowed (i.e. 7 chars)

Then link the textbox to a VBA code such as:
Private Sub TextBox1_Change()
Dim lastrow As Integer
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
Cells(lastrow + 1, 1).Value = TextBox1.Text
End Sub

The code will allow the user to enter a new ticket number with your restrictions. Hope this helps!

 

[AndyTampa]

I came up with something similar for Data Validation, but neither of our solutions perform the task of idiot-proofing. Your solution will limit the input to 6 characters while not counting the space, but it still allows a 7 character input with a space in the middle.

I've come up with =AND(LEN(A2)=6,LEN(SUBSTITUTE(A2," ",""))=6), but that allows input using other characters such as periods and parentheses. I need this to be strictly alphanumeric characters.

The other formula only produces a zero result. I'm not looking to count how many cells have less than 6 characters. I'm looking to count how many cells have 6 alphanumeric characters only.

@gigidica, I can't use a VBA solution for this.

 

[jtakw]

The last formula in my Post #2 will Count only cells in range with exactly 6 characters, although 6 of Any characters.

This formula for DV won't allow spaces anywhere and only allow 6 characters, again, any characters.

Book3.xlsx
	A	B	C	D
1			FALSE	0
2	12 345		FALSE	6
3	123 45		FALSE	6
4	12345a		TRUE	6
5	123 456		FALSE	7
6	123456		FALSE	7
7	123456 7		FALSE	8
8	123456		FALSE	7
9	ab1234		TRUE	6
10	)(&..$		TRUE	6
Sheet788

Cell Formulas
Range	Formula
C1:C10	C1	=AND(ISERR(FIND(" ",A1)),LEN(A1)=6)
D1:D10	D1	=LEN(A1)

Before I can attempt to come up with further solutions, you'll need to provide the Exact format of your case numbers,
if there may be different variations, please provide samples for all.

 

[AndyTampa]

From what you're testing, I believe you already understand what I'm looking for. The exact format is 6 alphanumeric characters. Not 5. Not 7. No Punctuation. No special characters. No spaces.

012345
abcdef
a2c4e7

In the absence of Data Validation that will work, I need the error checking to compare the number of cells with valid entries to the total number of cells with any data to determine that all cells have valid data. Even with Data Validation, I still need the error-checking formula.

 

[jtakw]

So you're saying there's no uniformity to the case numbers, just 6 characters that are alpha, numeric, or alphanumeric?

012345
abcdef
a2c4e7

000001
100000
aaaaaa
zzzzzz
1a2b3c
zzzzz1
1sssss
AaBbCc

These are all Valid case numbers?

 

[AndyTampa]

They all are valid for case numbers. There was a time where they had a single letter with 5 numbers, but now cases have two letters with 4 numbers. There is no particular position to where the letters are.

For arguments sake, each position of the 6 characters can be 0-9 or A-Z. The only stipulation I know of at this point is that everything has to be in capital letters.

 

[jtakw]

I have 2 suggestions:

A column that Flags the row with erroneous case numbers, (you can even use this flag to Conditional Format and highlight the cell/row)
And, if you still want, a Count of all the erroneous case numbers in range (result in a single cell), but I would still need the additional column mentioned above.

Would this work for you?

 

[AndyTampa]

I understand the usefulness of additional columns, but this is supposed to be a simple template. If you're going to use a formula to flag those rows in a separate column, can't we just use the formula in the error-checking formula? I'm thinking along the lines of "Total non-empty cells in range" minus "Cells in range that comply with the requirements" must equal 0. The hardest part seems to be dealing with the requirements (A-Z or 0-9, and only 6 characters).

I welcome any suggestions to accomplish the task, but I'm hesitant to add anything that I'll have to explain so leadership can properly copy the submitted data to their master sheet.