Easy Explanation Notes with Examples

Add Input Restrictions to Validate Data

Main Lesson: How to do Data Validation in Excel?

In this example, we will illustrate how to add input restrictions to validate data. We will make the following restricts: starts with letter S and followed by 6 numbers only.

1- Select a range of cells (A2:A7).

2- Press Alt + A + V or alternatively, go to the Data tab, from the Data Tools section, select Data Validation.

3- The Data Validation box will appear. Select Custom from the Allow option and enter the following formula:

=AND(LEFT(A2)=”S”,LEN(A2)=7,ISNUMBER(VALUE(RIGHT(A2,6))))

4- Press OK.

5- Result. Excel will restrict the user to start with letter S and later maximum entry of 6 numbers only.

Explanation: The AND function in the above used formula has three arguments:

LEFT functionLEFT(A2)=”S” restrict to start with only letter S.

LEN functionLEN(A2)=7 forces the user to enter the string containing 7 characters.

ISNUMBER functionISNUMBER(VALUE(RIGHT(A2,6)) restrict to enter maximum 6 numbers.

Input Message/ Error Alert

Note: For customized input message or error message, go to the Input message and Error Alert tabs.

Previous Lesson: Prevent duplicate entries




Copyright © 2016 - 2019 Explainry.com | All Rights Reserved