This example will teach you how to use data validation to prevent users from entering the wrong product code.
- Select a range A2: A7.
- On the Advanced tab Data (data) click Data Validation (Data validation).
- Выберите Custom (Other) from the drop down list Allow (data type).
- Enter the formula shown below in the field Formula (Formula) and press OK.
=AND(LEFT(A2)="С",LEN(A2)=4,ISNUMBER(VALUE(RIGHT(A2,3))))=И(ЛЕВСИМВ(A2)="С";ДЛСТР(A2)=4;ЕЧИСЛО(ЗНАЧЕН(ПРАВСИМВ(A2;3))))Explanation:
- Function AND (And) takes three arguments.
- LEFT(A2)=»C» or LEFT(A2)=”C” – obliges the user to start the word with the letter “C”.
- ONLY(A2)=4 or DLSTR(A2)=4 – does not allow you to enter a value shorter or longer than four characters.
- ISNUMBER(VALUE(RIGHT(A2,3))) or SUM(MEANING(TRUESIMW(A2))) – obliges the user to complete the input value with three digits.
- RIGHT(A2,3) or RIGHT(A2) – extracts the 3 rightmost characters from the text.
- Function VALUE (VALUE) converts them to a number.
- ISNUMBER (ISNUMBER) checks if this value is really a number.
- Function AND (AND) returns TRUE if all conditions are true.
Since, before opening the data validation window, we selected the range A2: A7, Excel automatically copied the formula to all cells in that range.
- To be sure, select a cell A3 And click the Data Validation (Data validation).As you can see in the figure, this cell also contains a formula.
- Please enter an invalid product code.
Result: Excel issues an error message.
Note: To specify an input hint or text for an error alert, click the tab Input Message (Message for input) or Error Alert (Error message).