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
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 function –LEFT(A2)=”S” restrict to start with only letter S.
LEN function –LEN(A2)=7 forces the user to enter the string containing 7 characters.
ISNUMBER function –ISNUMBER(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