Limit Excel cell entry to a certain number of digits

Question

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.

Answer 1

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.

Answer 2

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:

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.