Nov 03 2021 11:11 PM
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
Nov 04 2021 12:12 AM
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.