Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Iowa, USA
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Restricting cell contents to numbers (XL 2000)

    I did a search of the lounge and found a couple discussions that sort of, but not really, relate to the problem I'm having.

    I have an Excel file that is going to be an on-line order form. I have one cell that needs to contain a 7-digit ID number. I have the Format, Cells, Number option set to Number with 0 decimal places (giving me whole numbers only, I'm assuming). I have Data, Validation, Settings set to Text Length equal to 7.

    This works great for restricting the number of characters...the validation notice pops up if there are either more or fewer than 7 characters. The problem is that I want it to also pop up the error message if the cell contains anything other than numbers (letters, spaces or special characters) and I can't seem to get it to do that.

    Is there a formatting setting somewhere that will do this? I know I could write a macro to check the value but I'd rather not if I didn't have to.

    Thanks!
    Sue

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Restricting cell contents to numbers (XL 2000)

    I think I would make the validation be a number between 1 and 9999999 and a custom cell format of 0000000. If they enter 123456, it would display as 0123456 instead of giving an error, but that's not too bad. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    Iowa, USA
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Restricting cell contents to numbers (XL 2000)

    Hmmm... there's a thought.

    It would have to be a number between 1000000 and 9999999 (no leading-0s number would be valid) but that should work!

    Thanks!
    Sue

Posting Permissions

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