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

Data Analysis

  • Filter Data Based on Date in Excel
  • How to create Gauge Chart in Excel
  • How to calculate average last N values in a table in Excel
  • Excel Frequency Function Example
  • Managing Conditional Formatting Rules in Excel

References

  • How to get first column number in range in Excel
  • Multi-criteria lookup and transpose in Excel
  • How to get last column number in range in Excel
  • How to get relative row numbers in a range in Excel
  • Extract data with helper column in Excel

Data Validations

  • Excel Data validation number multiple 100
  • Excel Data validation only dates between
  • How To Create Drop-down List in Excel
  • Data validation must not exist in list
  • Excel Data validation specific characters only

Excel Data validation no punctuation

by

Reject data containing punctuation mark

To use data validation to restrict punctuation, you can use a named range and a formula based on the FIND and COUNT functions.

Formula

=COUNT(FIND(xlist,A1))=0

Explanation

In the example shown, the data validation applied to C5:C10 is:

=COUNT(FIND(xlist,B5))=0

where xlist is the named range D5:D11.

How this formula works

Data validation rules are triggered when a user adds or changes a cell value. When a custom formula returns TRUE, validation passes and the input is accepted. When a formula returns FALSE, validation fails and the input is rejected with a popup message.

In this case, we have previous defined the named range “xlist” as D5:D11. This range holds characters that are not allowed.

The formula we are using for data validation is:

=COUNT(FIND(xlist,B5))=0

Working from the inside out FIND function is configured with xlist for “find text”, and cell B5 as the text to search. Because we are giving FIND an array with multiple values, FIND returns an array of result, one for each character in the named range “xlist”. For cell B5, the result from FIND looks like this:

{#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}

Each #VALUE error represents one character not found. If we try to enter, say, “demolition@”, which includes a restricted character, FIND returns:

{#VALUE!;11;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}

Note the second value in the array is now 11.

Next, the COUNT function returns the count of all numbers in the array. When the array contains no numbers (i.e. no restricted characters) COUNT returns zero, the expression returns TRUE, and data validation succeeds. However, When the array contains no numbers (i.e. there is at least one restricted character found) COUNT returns a number, the expression returns FALSE, and data validation fails.

 

Post navigation

Previous Post:

COSH function: Description, Usage, Syntax, Examples and Explanation

Next Post:

Excel Data validation require unique number

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

  • Invoice status with nested if in Excel
  • IFERROR function: Description, Usage, Syntax, Examples and Explanation
  • SWITCH function: Description, Usage, Syntax, Examples and Explanation
  • Check multiple cells are equal in Excel
  • How to use Excel NOT Function

Date Time

  • How to join date and text together in Excel
  • Get first day of month in Excel
  • Convert time to time zone in Excel
  • How to get workdays between dates in Excel
  • Excel Date & Time Functions Example

Grouping

  • Calculate conditional mode with criteria in Excel
  • Group numbers at uneven intervals in Excel
  • Categorize text with keywords in Excel
  • How to randomly assign people to groups in Excel
  • Running count group by n size in Excel

General

  • Select, Insert, Rename, Move, Delete Worksheets in Excel
  • Excel Default Templates
  • Creating and Opening an existing file in Excel
  • Subtotal by invoice number in Excel
  • How to calculate total from percentage in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning