Restricting certain special characters

Copper Contributor

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 validation, and played a little with VBA, but unfortunately nothing has worked the way I need it to. We need only these 2 characters restricted as they are creating issues for our txt file when trying to upload in to the system, and telling users to not use them doesn't work. TIA

1 Reply

@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.