+ Reply to Thread
Results 1 to 14 of 14

Cell Restrictions allow only numbers, no special characters

  1. #1
    Forum Contributor
    Join Date
    08-02-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    103

    Question 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
    - must be able to start with a 0

    Thank you

    KR
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    05-07-2014
    Location
    India
    MS-Off Ver
    MS Office 365
    Posts
    313

    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: copy to clipboard
    Please Login or Register  to view this content.

    Data VAlidation.PNG

    Hope this will meet your requirement, if not feel free to revert.
    Attached Files Attached Files
    Thanks
    Nisha Dhawan


    If you like my answer please click on * Add Reputation
    "If you can dream it, You can do it "

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Cell Restrictions allow only numbers, no special characters

    I didn't download your file.

    Try this...

    Data validation
    Allow: Custom
    Formula:

    =COUNT(-MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))=9
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Valued Forum Contributor
    Join Date
    02-07-2013
    Location
    Philippines
    MS-Off Ver
    Excel Online; 365
    Posts
    610

    Re: Cell Restrictions allow only numbers, no special characters

    hi nisha it does not allow the number to start with 0
    If I've helped U pls click on d *Add Reputation

  5. #5
    Valued Forum Contributor
    Join Date
    02-07-2013
    Location
    Philippines
    MS-Off Ver
    Excel Online; 365
    Posts
    610

    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. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Cell Restrictions allow only numbers, no special characters

    That was one of the requirements:

    - must be able to start with a 0

  7. #7
    Valued Forum Contributor
    Join Date
    02-07-2013
    Location
    Philippines
    MS-Off Ver
    Excel Online; 365
    Posts
    610

    Re: Cell Restrictions allow only numbers, no special characters

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

  8. #8
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    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. #9
    Forum Contributor
    Join Date
    08-02-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    103

    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. #10
    Valued Forum Contributor
    Join Date
    02-07-2013
    Location
    Philippines
    MS-Off Ver
    Excel Online; 365
    Posts
    610

    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. #11
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Cell Restrictions allow only numbers, no special characters

    Quote Originally Posted by karimel_romeo View Post
    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. #12
    Forum Contributor
    Join Date
    08-02-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    103

    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. #13
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Cell Restrictions allow only numbers, no special characters

    You're welcome. Thanks for the feedback!

  14. #14
    Valued Forum Contributor
    Join Date
    05-07-2014
    Location
    India
    MS-Off Ver
    MS Office 365
    Posts
    313

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Average calculation of numbers with special characters
    By Ash248 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-06-2013, 06:47 PM
  2. [SOLVED] Calculating the average for numbers with special characters.
    By Ash248 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-06-2013, 01:10 PM
  3. [SOLVED] i want re alter my numbers with special characters
    By vengatvj in forum Excel General
    Replies: 20
    Last Post: 09-30-2013, 06:51 PM
  4. How to calculate only values with numbers not special characters like!
    By toplisek in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-04-2013, 06:43 AM
  5. [SOLVED] remove spaces (special characters) after numbers
    By fareastwarriors in forum Excel General
    Replies: 13
    Last Post: 11-04-2012, 09:25 PM

Bookmarks

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