Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Count rows with at least n matching values
  • How to use Excel VLOOKUP Function
  • How to calculate two-way lookup VLOOKUP in Excel Table
  • Offset in Excel
  • How to use Excel ROW Function

Data Analysis Examples

  • How to combine 2 or more chart types in a single chart in Excel
  • Conditional Formatting New Rule with Formulas in Excel
  • Data Series in Excel
  • Managing Conditional Formatting Rules in Excel
  • How to calculate average last N values in a table in Excel

Data Validation Examples

  • Excel Data validation no punctuation
  • Excel Data validation number multiple 100
  • Prevent invalid data entering in specific cells
  • Excel Data validation unique values only
  • Excel Data validation only dates between

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.

Worked Example:   FIND, FINDB functions: Description, Usage, Syntax, Examples and Explanation

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.

Worked Example:   Find function vs Search function in Excel

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.

Worked Example:   How to get top level domain (TLD) in Excel

 

Post navigation

Previous Post:

Data validation must not exist in list

Next Post:

Excel Data validation number multiple 100

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
  • Replace one character with another in Excel
  • How to extract multiple lines from a cell in Excel
  • How to abbreviate names or words in Excel
  • Remove last characters from right in a cell in Excel
  • How to count specific characters in a range in Excel
  • YEAR function: Description, Usage, Syntax, Examples and Explanation
  • Calculate total hours that fall between two times in Excel
  • Create date range from two dates in Excel
  • Display the current date in Excel
  • Convert text date dd/mm/yy to mm/dd/yy in Excel
  • DOLLARFR function: Description, Usage, Syntax, Examples and Explanation
  • How to set check register balance in Excel
  • How to calculate principal for given period in Excel
  • YIELDDISC function: Description, Usage, Syntax, Examples and Explanation
  • NOMINAL function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2022 xlsoffice . All Right Reserved. | Teal Smiles