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.

 

[Gajendran Yadhav]

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

any assistance over this will be much appreciated as well....

 

[Gajendran Yadhav]

Dear Peter & Rick,

after going through several pages over, finally I got the code that works for any sequence.

=ISNUMBER(SUMPRODUCT(FIND(MID(G1,ROW(INDIRECT("1:"&LEN(G1))),1),"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ")))

My only concern is that where should I add the LEN function to restrict the character length to 16 in this code...??

Thanks in advance.

 

[Peter_SSs]

where should I add the LEN function to restrict the character length to 16

It would be

=AND(LEN(G1)=16,ISNUMBER(SUMPRODUCT(FIND(MID(G1,ROW(INDIRECT("1:"&LEN(G1))),1),"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"))))

But it could be done a bit shorter like this

=AND(LEN(G1)=16,COUNT(FIND(MID(G1,ROW(INDIRECT("1:16")),1),"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"))=16)

However, both of these take allow any combination of 16 digits or upper case letters (eg 1234567896549872). That is, hey do not take account of what you wrote in post #20

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.

If you do actually require always starting with alphabets then since alphabets is written as plural, how many alphabets must be at the start?

 

[Gajendran Yadhav]

=AND(LEN(G1)=16,ISNUMBER(SUMPRODUCT(FIND(MID(G1,ROW(INDIRECT("1:"&LEN(G1))),1),"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"))))

This code works great. Thanks dear.

I figured out another way too...

=ISNUMBER(SUMPRODUCT(FIND(MID(Q5,ROW(INDIRECT("1:"&LEN(Q5))),1),"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ")))*(LEN(Q5)=16)

This also works for any sequence as your above code too.

If you do actually require always starting with alphabets then since alphabets is written as plural, how many alphabets must be at the start?

Initially we came across all the codes to be 4 alphabets followed by 12 numbers... after Rick or you asked if it'll be always so, i tried to decode the sequence of our coding system and learnt that it always isn't.

In general its 4 alphabets followed by 12 integers. At times it is 3 or 4 alphabets, a number, an alphabet and followed by 11 numbers. Seldom it's up-to 6 alphabets followed by 10 numbers where last four digits refer the year of code generation say like 2023 or 2022 or 2024...

Ex:
1) KABC010334912022 - 4 alphabets + 12 numbers
2) TNKI0E0007472023 - 4 alphabets + 1 number + 1 alphabet + 10 numbers
3) TNK1060003872015 - 3 alphabets + 13 numbers
4) WBCHCO0020272020 - 6 alphabets + 10 numbers.


Anyways the above formulas both of yours and what I've mentioned works for any sequence restricting the length for 16 character long.

It was a great support by you dear. Appreciate your kindness.

 

[Peter_SSs]

I figured out another way too...

=ISNUMBER(SUMPRODUCT(FIND(MID(Q5,ROW(INDIRECT("1:"&LEN(Q5))),1),"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ")))*(LEN(Q5)=16)

That really isn't another way. That is like saying
1+2=3 is different to 2+1=3
because * is another way of applying an AND function. So the two are basically identical, just written in a different order.
=AND(LEN(G1)=16,ISNUMBER(SUMPRODUCT(FIND(MID(G1,ROW(INDIRECT("1:"&LEN(G1))),1),"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"))))
=ISNUMBER(SUMPRODUCT(FIND(MID(Q5,ROW(INDIRECT("1:"&LEN(Q5))),1),"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ")))*(LEN(Q5)=16)

The other way that I suggested is still shorter though.
=AND(LEN(G1)=16,COUNT(FIND(MID(G1,ROW(INDIRECT("1:16")),1),"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"))=16)

 

[smozgur]

This code works great. Thanks dear.

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.