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.

 

[Rick Rothstein]

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

Argh!!! I guess I am too tired to think straight (I am about an hour past when I should have gone to sleep).

 

[Gajendran Yadhav]

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)))

Hi Peter.
Thanks for your help...but I'm not sure why this code is not working out for me...

 

[Gajendran Yadhav]

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


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

Yes it does as you say.... only after you mentioned I checked that...

anyways, rick's second code works good...

the original code allows for any string with first 4 chars - alphabets followed by integers.. it allows even 5 character length or 16 character length.
i just altered the last part (LEN(F1)=16).

It works good.

 

[Gajendran Yadhav]

**** 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)

Rick..

This works well...

I just changed the last part "(LEN(F1)<17)" to "(LEN(F1)=16" so that it doesn't allow an entry that has less than 16 characters.
It restricts to first 4 chars Alphabets and rest 12 chars to integers.

Thanks a lot for you guys...

Peter & Rick - My excel gurus

 

[Gajendran Yadhav]

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.

View attachment 98542

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
View attachment 98543

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...

and btw do I have any remedy for this...???

Column P should not accept any entry until column O is filled. moreover, column P takes any value apart from the data validation..

 

[Gajendran Yadhav]

ap

Argh!!! I guess I am too tired to think straight (I am about an hour past when I should have gone to sleep).

apologies that I'm eating your sleep... Thanks for your time mate.

 

[Peter_SSs]

This works well...

It restricts to first 4 chars Alphabets and rest 12 chars to integers.

I don't believe that it does, even after your change to set the length to 16. Try entering these values
ABCD13DECEMBER23 (I already gave this as an example earlier)
ABCD1.26859251E2
I believe that both of those values will be allowed (even by your modified formula) when they should not be.

.but I'm not sure why this code is not working out for me.

I suspect that you have not implemented it correctly. Here is a sample file that you can download and test with. The Data Validation has been applied to all of column Q from row 5 down.
Gajendran Yadhav DV Test.xlsx

 

[Gajendran Yadhav]

I don't believe that it does, even after your change to set the length to 16. Try entering these values
ABCD13DECEMBER23 (I already gave this as an example earlier)
ABCD1.26859251E2
I believe that both of those values will be allowed (even by your modified formula) when they should not be.


I suspect that you have not implemented it correctly. Here is a sample file that you can download and test with. The Data Validation has been applied to all of column Q from row 5 down.
Gajendran Yadhav DV Test.xlsx

Dear Peter,

OMG! yes, actually the excel allows this. I guess it takes the function to be DTAE and "E" for exponential... not sure though.
only after you said, I noted this...

I didn't think in a broad manner...just narrowed my look to the need. thanks for updating.

the excel file you shared over drop box - I'm unable to download as geo-fencing for certain websites in the work premises.
I'll check the file by tonight and update you dear friend.

I copied the formula you gave above...but I got error msg...attached screenshot for reference.

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

 

[Peter_SSs]

I copied the formula you gave above.

It looks like you did not follow this instruction that I gave you before:

if selecting from Q5 down, change each Q6 in my formula to Q5

Since it appears you may be selecting from Q1 and down then you need to change each Q6 in my formula to Q1.

If that active cell in your image is not Q1 then you need to change each Q6 in my formula to whatever that active cell is.

 

[Gajendran Yadhav]

I don't believe that it does, even after your change to set the length to 16. Try entering these values
ABCD13DECEMBER23 (I already gave this as an example earlier)
ABCD1.26859251E2
I believe that both of those values will be allowed (even by your modified formula) when they should not be.


I suspect that you have not implemented it correctly. Here is a sample file that you can download and test with. The Data Validation has been applied to all of column Q from row 5 down.
Gajendran Yadhav DV Test.xlsx

Hi Peter

just got the opportunity today to check the test fil that you shared. I'm really grateful.

And few more queries...
This is for the particular sequence of AAAAXXXXXXXXXXXX.
What if I need to use a different sequence say like WBCHCO0020272020 or TNKI0E0007472023 or any other combination...but always starting with alphabets and restricted to 16 characters long.