Data Validation Query: Only allow alphanumerics with fixed character limit/length

[tamjid]

Hi Everyone,

First of all, thanks if you are reading my query. I have been struggling to find a formula that will allow me to restrict cell entries only to alphanumeric and 12 characters. I have been searching in the forums and google and could only find solutions for alphanumeric or length or restricting the length with alphanumerics but that also with fixed condition such as the alphabets need to be the first characters, etc etc.

Requirement:
1. What I need is, the cell will allow only alphanumeric (no specific order or sequence). it can be "US2K34675896", "FYIJ9101112", anything.
2. The cell will only allow 12 characters (the allowable alphanumerics) to be entered, not less not more.

It will be a great help if you could provide me with a solution.

Thanks in advance

Tamjid

 

[Alex Blakenburg]

• In data validation
• Select custom
• In formula copy in the below
  

  =AND(LEN(A1)=12,ISTEXT(A1))

• Change A1 to whichever cell you are applying the validation to
  ie the input cell being validated

 

[JimM]

I've not come up with anything better but Alex suggestion does have a couple of issues

If the input is all numbers it will reject it - unless you start the string with a ' (this maybe what you wanted?)
It also allows the user to input 'special' characters eg *,!,/ which I'm assuming you don't want

 

[Peter_SSs]

Welcome to the MrExcel board!

There can be different interpretations of exactly what "alphanumeric" characters include/exclude. Guessing from your examples I have assumed upper case letters only plus digits 0-9.
If so, you could try this custom Data validation formula.

21 04 08.xlsm
	A
1	123GFTR546RE
2	US2K34675896
DV

Cells with Data Validation
Cell	Allow	Criteria
A1:A2	Custom	=AND(LEN(A1)=12,COUNT(FIND(MID(A1,SEQUENCE(LEN(A1)),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"))=12)

If lower case letters are also allowed, just change the FIND function to SEARCH in the formula.
Sometimes + - or . are also allowed in alphanumeric and if you want those just add them to the string in the formula.

 

[tamjid]

• In data validation
• Select custom
• In formula copy in the below
  

  =AND(LEN(A1)=12,ISTEXT(A1))

• Change A1 to whichever cell you are applying the validation to
  ie the input cell being validated

Thank you very much for your reply. The formula does not seem to work in my case as the ISTEXT function allows a wide spectrum of keyboard characters including letters from other languages as well. It still keeps return a TRUE statement for whatever I input. However, it does take the character limit in consideration with no doubt. Cheers.

 

[tamjid]

I've not come up with anything better but Alex suggestion does have a couple of issues

If the input is all numbers it will reject it - unless you start the string with a ' (this maybe what you wanted?)
It also allows the user to input 'special' characters eg *,!,/ which I'm assuming you don't want

Absolutely. I only want to allow English alphabets and numbers in the cell as inputs.

 

[tamjid]

Welcome to the MrExcel board!

There can be different interpretations of exactly what "alphanumeric" characters include/exclude. Guessing from your examples I have assumed upper case letters only plus digits 0-9.
If so, you could try this custom Data validation formula.

21 04 08.xlsm
	A
1	123GFTR546RE
2	US2K34675896
DV

Cells with Data Validation
Cell	Allow	Criteria
A1:A2	Custom	=AND(LEN(A1)=12,COUNT(FIND(MID(A1,SEQUENCE(LEN(A1)),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"))=12)

If lower case letters are also allowed, just change the FIND function to SEARCH in the formula.
Sometimes + - or . are also allowed in alphanumeric and if you want those just add them to the string in the formula.

This is exactly the one formula I was looking for. Thank you so much for helping me out. I was seriously struggling with this for the last two days. I am an excel noobie, therefore did not have the courage to post. This absolutely worked. Thanks again. Cheers.

 

[Peter_SSs]

This is exactly the one formula I was looking for. Thank you so much for helping me out.

You are welcome. Glad it worked for you. Thanks for the follow-up.

I am an excel noobie, therefore did not have the courage to post.

The forum is as much (or more) for noobies than intermediate or advanced users so feel comfortable asking any questions that you have.

 

[Gajendran Yadhav]

Welcome to the MrExcel board!

There can be different interpretations of exactly what "alphanumeric" characters include/exclude. Guessing from your examples I have assumed upper case letters only plus digits 0-9.
If so, you could try this custom Data validation formula.

21 04 08.xlsm
	A
1	123GFTR546RE
2	US2K34675896
DV

Cells with Data Validation
Cell	Allow	Criteria
A1:A2	Custom	=AND(LEN(A1)=12,COUNT(FIND(MID(A1,SEQUENCE(LEN(A1)),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"))=12)

If lower case letters are also allowed, just change the FIND function to SEARCH in the formula.
Sometimes + - or . are also allowed in alphanumeric and if you want those just add them to the string in the formula.

Dear Peter,

I was looking for something similar & found your guidance here....
But I'm facing some trouble using this formula...

My requirements - 16 character length Alphanumeric values. No special characters including "-".
The cell should contain both Alphabets & Numbers - Eg.: MHPU040308782015
Mostly in a similar sequence...However I'm not relying on the sequence...but it should take both Numbers & Alphabets. If only alphabets or numbers - it should return a error message and clear contents.

Any help would be appreciated. Thanks in advance.

 

[Peter_SSs]

I was looking for something similar & found your guidance here....
But I'm facing some trouble using this formula...

My requirements - 16 character length Alphanumeric values. No special characters including "-".
The cell should contain both Alphabets & Numbers - Eg.: MHPU040308782015

Duplicate to: Limiting the use of Special Characters

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.