Quick, clean, and to the point

Data validation specific characters only

Excel formula: Data validation specific characters only
Generic formula 

To use data validation to allow a list of specific characters only, you can use a rather complicated array formula based on the COUNT, MATCH, and LEN functions. In the example shown, data validation is applied with this formula:


where "allowed" is the named range D5:D11.


Working from the inside out, the MID function is used to generate an array from text entered in B5 with this snippet:


explained in detail here. The result is an array like this:


which goes into MATCH as the lookup value. For lookup array, we use the named range "allowed", concatenated to an empty string (""):


The concatenation converts any numbers to strings, so that we are matching apples-to-apples. The result is an array like this:


The last argument in MATCH, match_type is set to zero to force an exact match. Because we give MATCH multiple lookup values, we get back an array with multiple results:


Each number in this array represents a match. In the event a match isn't found for a character, the array will contain a #N/A error.

Finally, the COUNT function is used count the numbers in the result array, which is compared to a count of all characters in the cell calculated with the LEN function. When MATCH finds a match for all characters, the counts are equal, the formula returns TRUE, and data validation succeeds. If MATCH doesn't find a match any character, it returns #N/A instead of a number. In that case, the counts don't match and data validation fails.

Note: this formula relies on brute force to get the job done. If you have a better approach, please leave a comment below.

Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.