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?
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
Hi,

For DV, you can use this formula:

Excel 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:

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

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

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

gigidica

New Member
Joined
Oct 14, 2014
Messages
10
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)
1614202676299.png

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

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

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

ADVERTISEMENT

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
ABCD
1FALSE0
212 345FALSE6
3123 45FALSE6
412345aTRUE6
5123 456FALSE7
6123456FALSE7
7123456 7FALSE8
8123456FALSE7
9ab1234TRUE6
10)(&..$TRUE6
Sheet788
Cell Formulas
RangeFormula
C1:C10C1=AND(ISERR(FIND(" ",A1)),LEN(A1)=6)
D1:D10D1=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

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

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

ADVERTISEMENT

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

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

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
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

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

Watch MrExcel Video

Forum statistics

Threads
1,129,563
Messages
5,637,077
Members
416,956
Latest member
mitzhaki

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