Allow alphabets and alphanumeric only

**venkataram** · 10-19-2011, 09:21 AM

Hi

I need two formulae in Data validation

1. Cell should allow only alphabets (a-z and A-Z) and should not allow special charecters like ~,%,$ etc.,
2. Cell should allow only alphabets, numeric values (a-z , A-Z, 0-9) and should not allow special charecters like ~,%,$ etc.,

these things I need to implement in my project urgently..
Please help

Thanks in advance

**NBVC** · 10-19-2011, 09:37 AM

The simplest way would be to list the alphabet in a column (no need to do both upper and lower, one type will suffice) and then underneath list the numbers 0-9

You can name the alpha range, e.g. ALPHA and the alpha-numeric range as ALPHANUM

then data validation, choose custom and enter formula:

=MATCH(A1,ALPHA,0) for alpa condition

and

=MATCH(A1,ALPHANUM,0) for the alphanumeric condition

where A1 is top-left most cell you select to apply validation to.

**Kyle123** · 10-19-2011, 09:42 AM

NBVC's way is the best, but if you need additional flexibility, try the below:

Please Login or Register  to view this content.

**KatyPerryFan** · 07-12-2017, 02:11 PM

Can anybody help me with the Data Validation formula for hex codes please, I want my hex codes to come like ex: 4C4F56, not 4c4f56... but I don't know the formula to put in to get it to go like I want - HELP ME PLEASE!

**narsingh** · 01-18-2018, 05:53 AM

use this formula then it will work fine :-

=IF(A2="",TRUE,IF(ISERROR(SUMPRODUCT(SEARCH(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),"0123456789abcdefghijklmnopqrstuvwxyz"))),FALSE,TRUE))

**ngemenk** · 02-27-2020, 02:57 AM

Originally Posted by narsingh

use this formula then it will work fine :-

=IF(A2="",TRUE,IF(ISERROR(SUMPRODUCT(SEARCH(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),"0123456789abcdefghijklmnopqrstuvwxyz"))),FALSE,TRUE))

Awesome, event i don't understand how this can works, but this is what i looking for... GREAT