Data validation

Hello Ben,

Thank you for your question.

You can create a custom data validation rule in Excel to ensure that your custom code adheres to the specified format. Here’s how you can achieve this:

Select the cells where you want to apply the validation (the cells where users will input the custom codes).

Open the Data Validation dialog box:
Click the Data tab.
In the Data Tools group, click the Data Validation button.

On the Settings tab of the Data Validation dialog window:
Select Custom in the Allow box.

Enter the following formula in the Formula box:

=AND(LEN(A1)=4, ISNUMBER(VALUE(MID(A1, 3, 2))), CODE(MID(A1, 1, 1)) >= 65, CODE(MID(A1, 1, 1)) <= 90, CODE(MID(A1, 2, 1)) >= 65, CODE(MID(A1, 2, 1)) <= 90)

In the above formula, A1 represents the topmost cell of the range you want to validate.

Click OK to apply the custom data validation rule.

Here is an explanation of the formula:

LEN(A1)=4: Ensures that the code has exactly four characters.

ISNUMBER(VALUE(MID(A1, 3, 2))): Validates that the third and fourth characters are numeric.

CODE(MID(A1, 1, 1)) >= 65 and CODE(MID(A1, 1, 1)) <= 90: Checks that the first character is an uppercase letter (ASCII codes for uppercase letters).

CODE(MID(A1, 2, 1)) >= 65 and CODE(MID(A1, 2, 1)) <= 90: Verifies that the second character is also an uppercase letter.

Now, when users enter custom codes like “TO01,” “BR02,” or “XA12,” Excel will validate whether they match the specified format. If not, an error message will appear.

Feel free to adjust the formula or customize the error message as needed!

I hope this helps. Please let us know if you need further assistance.

Kind regards
Marius Barnard
STL