0

How can one fix the number of digits entered in a cell. For instance, I want only 9 digits to be entered in a cell and if it's more or less it should be flagged or not accepted. I want to make sure that junior staff don't miss digits when entering data.

3

You can use the data validation (Ribbon > Data > Data Validation) to control it.

Allowing a 9 digit number only, is equivalent to allowing numbers between 100,000,000 and 999,999,999.


If you want to simply flag it instead, you should use a conditional formatting, if the number is not between 100,000,000 and 999,999,999.

-3

If you want to Fix the number of digits to be entered in Cell or Range of cells, You need to follow these steps:

  1. Select the Cell/ Range of Cells.
  2. Click Data ribbon & hit Data Validation.
  3. Ensure you're on the Settings tab of the Data Validation dialog.
  4. From the Allow drop-down click CUSTOM.
  5. In the FORMULA text box enter this Formula:

    =AND(ISNUMBER(--D76:D82),LEN(D76:D82)=9)

  6. Set INPUT & ERROR MESSAGE as you wish.
  7. Finish with OK.

Note,

  1. Above shown Data validation allow you to enter ANY VALUE but MAXIMUM up to 9 Digits.

  2. If you try to enter any value is more or less than 9 Digits, Excel will show an error message set by you.

  3. Adjust the data range in the Validation formula as your need.

Check the Screen Shots:

enter image description here

N.B. Just check the Screen Shot In the first cell Excel allows to enter the value 121245789 which has 9 digits but show error message for value 5485478595 which has 10 digits.

  • 3
    Once again, you didn't read the question carefully and posted a non-responsive answer. This gives the wrong result for an 8 digit number. – fixer1234 May 5 '18 at 7:41
  • @fixer1234,now I've edited the Formula =9. Exactly as OP written, "I want only 9 digits to be entered in a cell and if it's more or less it should be flagged or not accepted." – Rajesh S May 5 '18 at 8:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.