How do you restrict special characters in a cell?

[awalker25]

How do you restrict special characters ($, %, etc.) in excel? What is the formula for that?

 

[BlueHornet]

Welcome to the board.

If you format a cell or range of cells for "number" (with no thousands separator), unprotect those cells and then use data validation to ensure that only positive whole numbers can be entered, then protect the worksheet ... that'll do it.

It's not that you can't even type the ,$.- etc., but they won't be held. All you'd be able to enter (and later view) would be positive whole numbers.

Something like that?

 

[awalker25]

Not exactly. I am trying to restrict users from enetering in "$" within a cell; however I am not sure of the formula for that!

 

[T. Valko]

Not exactly. I am trying to restrict users from enetering in "$" within a cell; however I am not sure of the formula for that!

It might be easier to define what characters ARE ALLOWED as opposed to what characters are not allowed.

So, what characters are allowed?

 

[p45cal]

One way:

Excel Workbook
	A	B	C
62	as3asddf	0	TRUE
63	as!asd#	2	FALSE
Sheet

#VALUE!

The Data Validation is the only formula you need, the others are there to show how it works. Invalid characters lumped together in a single string highlighted in bold blue above. You can add your own messages in Data Validation which (a) pop up in a yellow box when you select the cell and (b) a similar message to indicate why the entry was rejected.

 

[pulec]

One way:

Excel Workbook
	A	B	C
62	as3asddf	0	TRUE
63	as!asd#	2	FALSE
Sheet

#VALUE!

The Data Validation is the only formula you need, the others are there to show how it works. Invalid characters lumped together in a single string highlighted in bold blue above. You can add your own messages in Data Validation which (a) pop up in a yellow box when you select the cell and (b) a similar message to indicate why the entry was rejected.

mustr1.xlsx
	A	B
1	1234ABcd	TRUE
2	123 4Abcd	FALSE
List1

Cell Formulas
Range	Formula
B1:B2	B1	=LEN(A1)-SUMPRODUCT(--(ISNUMBER(FIND(MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1),"/0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ-"))))=0

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=List1!$A$1:$A$1016	B1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1:B2	Cell Value	=FALSE	text	NO

This is awesome p45cal,
I used it with a tweak to make it a stricter control - only allow listed characters.
Thank you!