How to restrict special character and allow only numbers

Original Question:- 

How to restrict special character and allow only numbers?

Can someone please look at the attached and create a validation that will allow the following:
- only numbers
- only numbers 0 to 9
- no special characters of any kind (,”:#$%^&* etc)
- no more than 9 numbers – if so error message
- no less than 9 numbers – if so error message
- must be able to start with o

image 1

 

To meet this requirement we use “AND”, “LEN”, “VALUE” function in Data Validation.

Follow below given steps:-

  • Select the range A17:A20.
  • Go to Data tab > DataValidation > Settings
  • Select the formula in Custom category.
  • =AND(LEN(B13)>=9,LEN(B13)<=10,VALUE(B13)>0)

image 2

 

When we will enter the value below to 9 digit or special character, alpahabet and above to 9 digit then it will give an error and will not except the entered value in cell.

 

image 3

 

 

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube