LinkBack URL
About LinkBacks
I want to restrict user from entering alphabets like I,O,Q and S in a cell. Can we do this using data validation or any formula because I don't wan't to use drop-down list or refer to values in other cells either.
I want to restrict user from entering alphabets like I,O,Q and S in a cell. Can we do this using data validation or any formula because I don't wan't to use drop-down list or refer to values in other cells either.
If you want numbers only, how about you do data validation. Then select whole number and use the criteria "Greater Than or Equal to" and select 0.
welcome to the forum. is it case sensitive? like if it's
q
is it accepted?
is 1 single letter not allowed or none of those letters can be found? eg
abQc
is it accepted?
select the cells you want the dropdown applied to (say A1:B5). go to Data -> Data Validation -> Allow: Custom-> Formula:
non-case sensitive & none of the letters can be found:
=COUNTIF(A1,"*I*")+COUNTIF(A1,"*O*")+COUNTIF(A1,"*Q")+COUNTIF(A1,"*S*")=0
none-case sensitive & single letter:
=AND(A1<>"I",A1<>"O",A1<>"Q",A1<>"S")
case-sensitive & none of the letters can be found
=AND(ISERR(FIND("I",A1)),ISERR(FIND("O",A1)),ISERR(FIND("Q",A1)),ISERR(FIND("S",A1)))
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
Thank you benishiryo for the welcome and also for the prompt solution. Its exactly what I wanted and works fine for me. Sorry for providing less information, yes its only single letter and non-case sensitive
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks