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]

I think I have found it, I used the formula

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

Although you have added some more special characters, that is the exact same formula as you had in your first post, meaning it should produce the same error. if it is not causing the error then that strongly suggests that the formula in post 1 was not the exact formula that was causing the error which in turn explains why it was not possible to find the cause.

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.

When I use that formula as a validation rule for B5 it prevents any of the listed characters from being entered with or without text.

 

[dpaton05]

I am not sure what I did as using the formula now gives me an error with anything entered.

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

Would it be best to try a vba solution as I cannot find any function that solves my problem?

 

[jasonb75]

When I tried that formula it worked as required. If it is not working for you then chances are a vba alternative will encounter the same problem.

Try setting up the same validation rule in a new workbook and see if it works there.

 

[dpaton05]

It even did it on a new workbook. My boss has told me that I can use the input text on that cell instead so thanks for your help.