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

img1

  • 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.

img2

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

img3

  • The Data Validation dialog box will appear

img4

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

img5

  • 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

img6

  • 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.

img7

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

img8

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.

Users are saying about us...

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube