Formula to go in the data validation that prevents special characters from being entered in a cell

[dpaton05]

I need to prevent certain special characters from being entered into B5. I want to do this with data validation.

This is the formula I used but it gave me an error saying A named range you specified cannot be found.

=AND(ISERROR(SEARCH(MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1),"\/:%'*?<>|"".")))

If I removed the = sign, I didn't get that error, but it won't let me enter anything in the cell.

Can someone help me please?

 

[jasonb75]

Works fine for me, perhaps the error is being caused elsewhere?

 

[dpaton05]

Where could I look?

 

[dpaton05]

In the data validation settings box, on the input message tab, I have a message that is displayed and I have an error message that is displayed on stop.

 

[jasonb75]

The error is nothing to do with the validation. If it is happening when you enter data into the cell then my best guess would be that you have another INDIRECT formula somewhere that is using the entry in the cell to form a range.

Try selecting the validation cell, then clicking 'Trace dependents' on the Formulas tab.

 

[dpaton05]

I am trying a different formula and it seems to block most special characters. The only ones I will not stop from being entered are ~ * ?

I need them blocked from entry
Also, full stops and commas are blocked but I need to allow them.

This is the formula
=ISNUMBER(SUMPRODUCT(SEARCH(MID(B5,ROW(INDIRECT("6:"&LEN(B5))),1)
," "&"0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ")))

Thanks

 

[dpaton05]

Try selecting the validation cell, then clicking 'Trace dependents' on the Formulas tab.

This found no formulas to refer to the range.

 

[dpaton05]

I think I have found it, I used the formula

=AND(ISERROR(SEARCH(MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1),"`~!@#$%^&*()_+-=[]\{}|;’:”<>/?")))

 

[dpaton05]

Actually, with the above formula, if I enter the special characters on their own, I get the error but if I enter them with some text, I do not get an error. Therefore, it allows them and it shouldn't, so the above formula doesn't work.

 

[dpaton05]

I found a formula that appears to be working at the moment.

ISNUMBER(SUMPRODUCT(FIND(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),",.’abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ ")))