Lesson 69: How to create product codes in Excel

In this article, I will show you how to use data validation to prevent users from entering incorrect product codes in Excel.

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.