In this article, I will show you how to use data validation to prevent users from entering incorrect product codes in Excel.
| Join the channel Telegram of the AnonyViet 👉 Link 👈 |
How to create product codes in Excel
1. Select the area A2:A7.
2. On the Data tab, in the Data Tools group, click Data Validation.
3. In Allow, click Custom.
4. In Formula, enter the formula shown below and click OK.
Explain:
- This AND function has three parameters. LEFT(A2) = “C” forces the user to start with the letter C. LEN(A2) = 4 forces the user to enter a string of length 4 characters.
- ISNUMBER(VALUE(RIGHT(A2,3))) forces the user to end with 3 numbers.
- RIGHT(A2,3) extracts the rightmost 3 characters of the text. The VALUE function converts this text string to a number.
- ISNUMBER checks if this value is a number.
- The AND function returns TRUE if all of the above conditions are true. Because I selected the range A2:A7 before clicking Data Validation, Excel automatically copies the formula to other cells.
5. To check, select cell A3 and click Data Validation.
6. Incorrect product key entered.
Result:
To change the error alert text, go to the Input Message and Error Alert tabs.
In addition, you can also view many other excel articles here.
