Forum Discussion

Copper Contributor

Nov 04, 2021

Restricting certain special characters

Hey there all, I'm stuck with trying to use either data validation or VBA to restrict users from using the following 2 special characters in certain cells: , " I have tried many formulas in data val...

excel

Riny_van_Eekelen

Platinum Contributor

Nov 04, 2021

mogollen

Let's assume the first cell you want to validate is B5, then use this formula to set up a custom data validation rule:

=SUM(IFERROR(SEARCH(MID(B5,SEQUENCE(LEN(B5)),1),","""""),0))=0

If your Excel version doesn't support the SEQUENCE function use this one:

=SUM(IFERROR(SEARCH(MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1),","""""),0))=0

Now copy B5 to all other cells where you want to block users from entering a comma or a quote mark.