Retricting Special Characters using Data Validation

[MGupta1]

Hi,

I have a requirement to restrict all the following special characters from being entered into a cell:

\/:%'*?<>|".

Have tried a few formulae but none worked in restricting all of them

 

[VBA Geek]

Enter the following data validation, if in A1

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

 

[MGupta1]

Thanks so much it works fine!

 

[MGupta1]

Hi, there is one small issue with this formula...it works fine when any of these special characters are added towards the end or mid of the cell, However it fails to restrict this special character when entered in the beginning: '
for example, if I enter ' towards end of say First Row Name, then the formula restricts. However if I put 'First Row Name then it takes '
What can be the modified formula to restrict this condition. Rest all works fine.

Thanks

 

[VBA Geek]

Hi, there is one small issue with this formula...it works fine when any of these special characters are added towards the end or mid of the cell, However it fails to restrict this special character when entered in the beginning: '
for example, if I enter ' towards end of say First Row Name, then the formula restricts. However if I put 'First Row Name then it takes '
What can be the modified formula to restrict this condition. Rest all works fine.

Thanks

the issue is not in the formula. Excel recognizes the single apostrophe as being a special character which tells Excel to treat the rest of the string as text. Maybe you can deactivate this setting, try to google it