# Cell Restrictions allow only numbers, no special characters

1. ## Cell Restrictions allow only numbers, no special characters

Hi Guys,

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

Thank you

KR

2. ## Re: Cell Restrictions allow only numbers, no special characters

Hello Kerimal,

I have attached a document for your reference. Also, please refer to the below image.

Assuming you want the data validation in B13 then

Type following formula in Data validation--> Custom

Formula:
`Please Login or Register  to view this content.`

Data VAlidation.PNG

Hope this will meet your requirement, if not feel free to revert.

3. ## Re: Cell Restrictions allow only numbers, no special characters

Try this...

Data validation
Allow: Custom
Formula:

=COUNT(-MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))=9

4. ## Re: Cell Restrictions allow only numbers, no special characters

hi nisha it does not allow the number to start with 0

5. ## Re: Cell Restrictions allow only numbers, no special characters

This simple formula should work; however, it does not allow the number to start with 0:

=AND(ISNUMBER(M14),LEN(M14)=9)

6. ## Re: Cell Restrictions allow only numbers, no special characters

That was one of the requirements:

7. ## Re: Cell Restrictions allow only numbers, no special characters

and I'm assuming that meant being able to enter 012345678

8. ## Re: Cell Restrictions allow only numbers, no special characters

Yes!

Or, even this:

000000000

If the number string starts with leading zeros then it would have to be formatted as TEXT otherwise Excel would strip off any leading zeros from a numeric number.

9. ## Re: Cell Restrictions allow only numbers, no special characters

So is this solved or you guys still working on it? - it must have all of the requirements - thanks so much!

10. ## Re: Cell Restrictions allow only numbers, no special characters

The formula on post #3 should work but you need to change the cell format to Text

11. ## Re: Cell Restrictions allow only numbers, no special characters

Originally Posted by karimel_romeo
So is this solved
You're the only one that can determine that.

The formula I suggested in post #3 meets all the conditions described in post #1. You can assign custom error messages as desired.

12. ## Re: Cell Restrictions allow only numbers, no special characters

sure does - apologies, i didnt see that post. I changed the cell format to text and it works nicely! thank you

13. ## Re: Cell Restrictions allow only numbers, no special characters

You're welcome. Thanks for the feedback!

14. ## Re: Cell Restrictions allow only numbers, no special characters

Hi Bhenlee,

its allowing to start the number with 0 you just need to format it as below shown image:-

Capture.PNG

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1