On Tue, 20 Dec 2005 10:19:29 -0800, "Frank Cutre" wrote:

Hi,

I need to make sure that a cell contains NO SPECIAL CHARACTERS (including
the <SpaceBar) during data entry.

I've tried:
=len(cellReference)=len(substitute(cellReference, or(char(32),char(34),...etc
),"")

but Excel doesn't like the OR funtion (2nd parameter) inside SUBSTITUTE.

How do you test if a cell reference contains ANY of the "special text
characters" (from list below)?

space
double quote
number
dollar
percent
ampersand
apostrophe
open parenthesis
close parenthesis
asterisk
plus
comma
hyphen
period
forward slash
colon
semi-colon
less than
equal
greater than
at symbol
open square bracket
backslash
close square bracket
caret
underscore
single quote (under tilde)
open curly bracket
pipe (above backslash)
close curly bracket
tilde


Thanx for your time... (^_^)



If you want to use Data Validation, understanding it's limitations that someone
could copy/paste a value into that cell that did not meet the criteria, then
you could download and install Longre's free morefunc.xll from
http://xcell05.free.fr

If your cell to be validated is, for example, A1, then in some unused cell (for
example Z1) enter the formula:

=REGEX.FIND(A1,"\W")

This will return a 0 UNLESS there is one of your special characters in A1, in
which case it will return the position of that character.

Then use the Data Validation Formula Is: =Z1=0


--ron