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.

 

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

 

[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?

 

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

 

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