Forum Discussion

mogollen's avatar
mogollen
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 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

excel

1 Reply

  • Riny_van_Eekelen's avatar
    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.

Resources