Restricting the Number of Characters Entered into a Cell in Excel 2010

While working on reports, you want the users to enter specific information about their personal details to be entered in Excel worksheet. To prevent users from entering SSN of greater or lesser than 9 digits, we will use Excel’s built-in feature Data Validation.

Let us take an example:

SSN cannot be more than 9 digits to restrict the user to enter their SSN & prevent them from entering digits lesser than or greater than 9.
In column A, we have names &in column B we want the people to enter their SSN

While entering SSN, we want the user to enter 9 digits since SSN requires exactly 9 digits (not more or less than).
Select the cells in which Data Validation needs to be applied while entering SSN.

Click on Data ribbon
In Data Tools group, click on Data Validation

The Data Validation dialog box will appear

In Settings tab, select Custom in Validation criteria & enter the formula as =LEN(B2)=9 in Formula box

The above mentioned formula will prevent the users from entering digits lesser or greater than 9 digits.
Click on OK
If we enter exactly 9 digits in column B then cell will accept the numbers

While if we purposely enter more than 9 digits, then Excel will not allow entering& an error message will pop-up to notify the problem.

If we enter less than 9 digits then also Excel will not allow us entering& an error message will appear.

Using Excel’s built-in feature Data Validation, we can prepare customize reports for the user to enter the specific information which is useful for making reports.