Excel Data validation no punctuation

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.

Also See:   How to extract substring in Excel

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, “[email protected]”, which includes a restricted character, FIND returns:

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

Note the second value in the array is now 11.

Also See:   Extract last name from full name -- Manipulating NAMES 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.

Also See:   FIND, FINDB functions: Description, Usage, Syntax, Examples and Explanation

 

Leave a Reply

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