Exceljet

Quick, clean, and to the point

Data validation specific characters only

Excel formula: Data validation specific characters only
Generic formula 
=COUNT(MATCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),allowed&"",0))=LEN(A1)
Explanation 

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:

=COUNT(MATCH(MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1),allowed&"",0))=LEN(B5)

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

How this formula works

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

MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)

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

{"A";"A";"A";"-";"1";"1";"1"}

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

allowed&""

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

{"A";"B";"C";"1";"2";"3";"-"}

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:

{1;1;1;7;4;4;4}

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.

Author 
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.

Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Shortcuts Video Course
Excel Charts video training course
Video training for Excel Tables