Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How to calculate average last N values in a table in Excel
  • Excel Pie Chart
  • How to Create Column Chart in Excel
  • How to conditionally sum numeric data in an Excel table using SUMIFS
  • How to create dynamic reference table name in Excel

References

  • Get nth match with INDEX / MATCH in Excel
  • Complete List of Excel Lookup and Reference Functions, References and Examples
  • Lookup entire row in Excel
  • Excel Advanced Lookup using Index and Match Functions
  • How to get last row in text data in Excel

Data Validations

  • Excel Data validation allow uppercase only
  • Excel Data validation number multiple 100
  • Excel Data validation must not contain
  • Excel Data validation allow weekday only
  • How To Create Drop-down List in Excel

Excel Data validation specific characters only

by

Set criteria to accept specific characters only

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.

Formula

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

Explanation

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.

Worked Example:   Highlight blank cells in Excel

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.

Worked Example:   Highlight cells that end with in Excel

Post navigation

Previous Post:

Number and Text Filters Examples in Excel

Next Post:

Convert date string to date time in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • Return blank if in Excel
  • IFERROR function: Description, Usage, Syntax, Examples and Explanation
  • Nested IF function example in Excel
  • IF, AND, OR and NOT Functions Examples in Excel
  • How to use Excel OR Function

Date Time

  • Convert Excel time to decimal minutes
  • How to get Holiday Date from Year in Excel
  • How to get same date next year or previous year in Excel
  • Convert text date dd/mm/yy to mm/dd/yy in Excel
  • Get last weekday in month in Excel

Grouping

  • Group numbers at uneven intervals in Excel
  • Group arbitrary text values in Excel
  • Map text to numbers in Excel
  • How to randomly assign people to groups in Excel
  • Calculate conditional mode with criteria in Excel

General

  • AutoFit Column Width, AutoFit Row Height in Excel
  • List sheet names with formula in Excel
  • Zoom Worksheet in Excel
  • How to get random value from list or table in Excel
  • How to create dynamic worksheet reference in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning