How to Create Custom Excel Data Validation for Specific Characters Using VBA
Question details
The user needs to restrict spreadsheet cell inputs to a specific set of allowed characters (digits, the capital letter A, hyphens, slashes), limit numeric characters to a maximum of 16 digits, or strictly allow the exact word UNKNOWN.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating secure data entry forms or databases where highly specific formatting, such as serialized product codes or specific status markers, is required.
- Observed behavior
- Standard data validation options cannot naturally filter or evaluate individual characters within a single text string, requiring a custom VBA macro script to enforce the rule.
Ensure your workbook is saved as an Excel Macro-Enabled Workbook (.xlsm) so your VBA function will persist, and make sure the Developer tab is visible in your Excel ribbon.
Use a Custom VBA Function for Data Validation
Creating a User Defined Function (UDF) in VBA is the most reliable way to evaluate strings for complex combinations of allowed characters and specific digit limits.
Because standard formula-based data validation struggles with evaluating every single character inside a string for multiple distinct rules, writing a dedicated VBA function provides the exact character-level control needed for this scenario.
Press Alt + F11 on your keyboard to open the Microsoft Visual Basic for Applications (VBA) editor window.
In the top menu, click on Insert, and then select Module to create a blank workspace for your custom script.
Write a VBA function (e.g., Function IsOK(val As String) As Boolean) that first checks if the input is exactly 'UNKNOWN'. If it is not, the code should loop through each character to ensure it is a digit, the letter 'A', a hyphen, or a slash, while simultaneously counting the digits to ensure they do not exceed 16. Return True only if all conditions are met.
Return to your worksheet and select the target cell (e.g., B2). Navigate to the Data tab, click Data Validation, choose 'Custom' from the Allow dropdown menu, and enter '=IsOK(B2)' in the Formula box.
Go to File > Save As, and choose 'Excel Macro-Enabled Workbook (*.xlsm)' from the file format dropdown to ensure your VBA function is permanently saved.
Set Up Custom VBA Data Validation Easily in WPS Spreadsheet
WPS Spreadsheet offers robust, native support for VBA macros, allowing you to easily build custom data validation logic. Its lightweight nature ensures smooth macro execution even on large datasets without the premium price tag.
- 1. Launch Developer Tools: Open your workbook in WPS Spreadsheet, navigate to the Developer tab, and click on VBA Editor.
- 2. Create the Validation Module: Right-click in the Project Explorer window, choose Insert > Module, and paste your custom character-checking VBA script.
- 3. Assign Rule to Cells: Highlight your target input cells, go to the Data tab, select Validation, and use the 'Custom' option to link your newly created VBA function.
Frequently Asked Questions
Why does standard Excel data validation fail for this task?
Built-in validation handles simple limits like text length or number ranges but lacks regex-style pattern matching required to check individual characters (like allowing 'A' but blocking 'B') dynamically across a text string.
Why did my custom data validation stop working after reopening the file?
If you saved the file in the standard .xlsx format, the software automatically removes any embedded VBA code. To preserve your validation function, you must always save the document as a Macro-Enabled Workbook (.xlsm).
Can I apply this VBA validation rule to an entire column at once?
Yes. Select the entire column, open the Data Validation dialog, choose Custom, and input the formula referencing the first active cell (e.g., =IsOK(A1)). The application will automatically adjust the relative references for the rest of the cells in that column.
