Alphanumeric with Character Length Restriction

[Gajendran Yadhav]

Dear friends,

I'm working on a sheet where I've to fill data based on daily status of the work.

for each category of task, I'm having an Alphanumeric code of 16 digits.

for ease of understanding I've attached the screen shot of the particular coulmn.

Here in this workbook, I've Data Validation function in Column O and dependent data validation in Column P.
I've a macro program such that if value in Column O is changed after an initial entry, column P gets cleared off. [using clear contents( )]
Moreover, the entire sheet is Cut,, Copy & Paste protected by using a Macro that I found in other thread here.

In the Column Q [CNR], I want to make the 16 digit Codes that accepts only alphanumeric values. No dashes, no spaces or any other special characters including ? & *.
i.e., A-Z & 0-9.
Character length limitation - 16.

Any help will be appreciated. thanks.

 

[nemmi69]

You could simply control the column with a data validation

 

[Gajendran Yadhav]

You could simply control the column with a data validation
View attachment 98536

Thanks for your reply... I've enabled this option for Char Limitation of 16.
this doesn't control what characters I input... this accepts everything including "SPACE".

I want character restriction to A-Z & 0-9 only. no small cases / no other symbols etc.

In the data validation, by using this formula
=ISNUMBER(SUMPRODUCT(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ")))
I'm able to control the character restriction to AA-Z & 0-9.
Is there any possibility to restrict the character length as well by using LEN()=16 in the above formula...
also the sequence of code I'm gonna use should contain both alphabets and numbers...
Eg: MHPU040061322016 ; TNMD100002612016 ; UPRP040190182018 etc...

 

[Rick Rothstein]

Is it ALWAYS four uppercase letters followed by twelve digits?

 

[Gajendran Yadhav]

Is it ALWAYS four uppercase letters followed by twelve digits?

Dear Rick,
Yes... It's always AAAAXXXXXXXXXXXX format or Blank if no data is available.

and in the above formula, unfortunately this error shows up.

Thanks

 

[Rick Rothstein]

**** IGNORE - DOESN'T WORK CORRECTLY ALL THE TIME ****


I cannot test this because I do not have your version of Excel, but I think it should work. If I am right, this will insure that the code is 16 characters long, starts with 4 upper case letters and ends with 12 digits...

=ISNUMBER(SUMPRODUCT(FIND(MID(F1,ROW(INDIRECT("1:4")),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")))*ISNUMBER(-MID(F1&"A",5,12))

 

[Peter_SSs]

this will insure that the code is 16 characters long,

It does allow entries longer than 16 characters.
It also allows, for example ABCD13DECEMBER23

My approach is ..

=AND(LEN(Q6)=16,ABS(77.5-CODE(MID(Q6,ROW(INDIRECT("1:4")),1)))<13,ISNUMBER(-MID(Q6,ROW(INDIRECT("5:16")),1)))

 

[Rick Rothstein]

**** IGNORE - DOESN'T WORK CORRECTLY ALL THE TIME ****

I think this should work correctly (Peter's formula is shorter though)...

=ISNUMBER(SUMPRODUCT(FIND(MID(F1,ROW(INDIRECT("1:4")),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")))*ISNUMBER(-MID(F1,5,12))*(LEN(F1)<17)

 

[Gajendran Yadhav]

I cannot test this because I do not have your version of Excel, but I think it should work. If I am right, this will insure that the code is 16 characters long, starts with 4 upper case letters and ends with 12 digits...

=ISNUMBER(SUMPRODUCT(FIND(MID(F1,ROW(INDIRECT("1:4")),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")))*ISNUMBER(-MID(F1&"A",5,12))

Dear Rick,
It works fine...but only for the Row 1 of any column. However I selected the entire column for Data Validation... i.e., Q5:Q1048576 (Column Q full except first four rows as it contains Title of the worksheet)

I found your code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
If Not Intersect(Target, Range("B5:B1048576")) Is Nothing Then
For Each Cell In Intersect(Target, Range("B5:B1048576"))
If Cell.Value Like "*[!A-Z0-9]*" Then
MsgBox "You have entered invalid CNR Number in cell " & _
Cell.Address(0, 0) & ". Please enter the correct 16 Digit CNR Number. " & _
"Only Alphanumeric values allowed.", vbCritical
Application.Undo
Exit Sub
End If
Next
End If
End Sub

and tried to add this to the existing macro... but this didn't turn to be favorable..

I'm unsure how to add another or new macro to the existing workbook...

Note:
Macro 1 - I'm using a macro that disabled Cut, Copy & Paste functions. -- this is successful.

Macro 2 - to clear contents in column P based on the value change in column O after first entry.
however this is not working to the perfection...yet it does work.
That means I'm able to type in some entry into the column P manually... which is not equal to the value of column P. It still accepts.

Subsequently when I change column O value, the contents in column P gets cleared off.
I've attached the screenshot for reference.

In Column O, the value should be either "By" or "Against".
Excel is accepting the same. I can type into it or select from the dropdown list.

Data Validation table

When I type any other string, it shows Error Dialog box.

but in the dependent cell, Column P, I'm able to type the value other than it has in the dropdown...See the attached pic.
ho to restrict this as well.
the subsequent column Q is the one which has 16 digit alphanumeric values in the sequence AAAAXXXXXXXXXXXX



Is there any means that I can upload my Excel worksheet here..?? so it may be useful for you to check my codes...

 

[Peter_SSs]

It works fine.

Are you sure? See my comments in post #7 about what that allows.

I suggest that you try the custom DV formula from my post above. I wasn't sure about the first row so if selecting from Q5 down, change each Q6 in my formula to Q5

I think this should work correctly ...

=ISNUMBER(SUMPRODUCT(FIND(MID(F1,ROW(INDIRECT("1:4")),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")))*ISNUMBER(-MID(F1,5,12))*(LEN(F1)<17)

It now allows entries less than 16 characters.
It also still allows ABCD13DECEMBER23