How do i prevent special characters from being input into a cell?

I have a spreadsheet where in one column the users must input alphanumerics, but no special characters, and another column where they should only input numbers. Both of these cases are issues where they have a tendency to input things that shouldnt be there which causes alot of problems with the rest of the spreadsheet, how do i prevent those things from being input into the cells (without using macros)?

Well for the first case, i mean just letters and numbers, no )(*#@$&(&^@!#& stuff. Case doesnt matter, length doesnt matter though it probably wont be more than 100 characters.

For the second case i just want it to be a number, nothing else.

if i had to pick which special characters i dont want, it would be...... / _ - , \ ( ) | { } [ ]

Originally Posted by Tony Valko

Try this...

First, create this named expression...

Goto the menu Insert>Name>Define
Name: String
Refers to: ="ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"
OK out

Apply the validation...

Let's assume you want this to apply to cell A1.

Select cell A1
Goto the menu Data>Validation
Allow: Custom
Formula:

=AND(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),String))

Uncheck: Ignore blank
OK out

If you get this warning: "The formula currently evaluates to an error. Do you wish to continue?" Just answer Yes.

If you want to test that formula as a formula in a worksheet cell it must be array entered.

Array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.


Do you want to allow ANY number like 2.5 or do you just want whole numbers like 2?

If you want ANY number then:

Data>Validation
Allow: Custom
Formula: =ISNUMBER(A1)

If you just want whole numbers then:

Data>Validation
Allow: Custom
Formula: =INT(A1)=A1

I entered it as a data validation in the cell as you described, but applied it to E2 instead of A1 like so:

=AND(SEARCH(MID(E2,ROW(INDIRECT("1:"&LEN(E2))),1),String))

and okayd through it, then put some special characters in the cell and they entered fine, so that didnt work.

Then i tried the ctrl shift enter method of putting the formula in the cell but it gave me a circular reference error.

The datavalidation number-only method works fine for my other problem though!