ASAP Utilities, a free Add-in available from www.asap-utilities.com has a
feature called "Advanced Character Removal" that should get rid of any
unwanted characters for you....

Vaya con Dios,
Chuck, CABGx3

Play with something like this array formula:

=MIN(LEN(SUBSTITUTE("ABC#DEFGH",{" ",".","#"},{"","",""})))<Len("ABC#DEFGH")

=len(cellReference)=len(substitute(cellReference,or(char(32),char(34),...etc

Frank Cutre wrote...

...

...

Looks like you want only letters and digits. There's no way to prevent
users from typing them, and if they're clever enough to know how to use
copy & paste, there's no way to use data validation to prevent them
from being entered. That leaves removing them yourself or forcing your
users to remove them.

Doing it yourself, there's no 1-cell way to do it without resorting to
VBA. You could use a udf like the following.

Function foo(s As String, p As String) As String
   Dim c As String * 1, k As Long, n As Long

   n = Len(s)

   For k = 1 To n
       c = Mid$(s, k, 1)
       If c Like p Then foo = foo & c
   Next k

End Function

and use it in formulas like

=foo(B5,"[A-Za-z0-9]")

The other alternative, make your users clean their own entries, can be
done by ensuring that invalid entries trigger nothing but errors in
formula results along with messages that tell the user that it's their
own invalid entries that caused the errors. That can be done with
built-in functions and one defined name, e.g., define the name N
referring to 128 or so, seq referring to

=ROW(INDEX($1:$65536,1,1):INDEX($1:$65536,N,1))

then try the *array* formula

=0/(COUNT(FIND(MID(UPPER(B5),seq,1),
"ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"))=N)

which should return 0 when cell B5 contains nothing but letters and
decimal numerals but #DIV/0! when it contains any other characters.

In my experience nothing is more effective in motivating users to enter
valid data than giving them nothing but errors and diagnostic messages
explaining that those errors are due to invalid entries. If a user can
only print off pages full of errors and text stating that the errors
are their own fault, they can't complain to their bosses or to IT
support. All they can do is fix their own errors. This won't win you
popularity contests among your users, but it will ensure they do what
they're supposed to do.

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