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.
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.
If you want to Fix the number of digits to be entered in Cell or Range of cells, You need to follow these steps:
- Select the Cell/ Range of Cells.
- Click Data ribbon & hit Data Validation.
- Ensure you're on the Settings tab of the Data Validation dialog.
- From the Allow drop-down click CUSTOM.
In the FORMULA text box enter this Formula:
- Set INPUT & ERROR MESSAGE as you wish.
- Finish with OK.
Above shown Data validation allow you to enter ANY VALUE but MAXIMUM up to 9 Digits.
If you try to enter any value is more or less than 9 Digits, Excel will show an error message set by you.
- 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.