We can use the Data menu Data validation to ensure the quality and accuracy of values entered in Google Sheets. This post describes how to restrict people from entering special characters in a selected cell range using Data validation in Google Sheets.
We will use the Regexmatch function to code a custom formula to deny the entry of any special characters in the selected range in Google Sheets.
We have multiple Data validation rules coded for your specific purposes. You can allow only specific special characters such as commas, periods, and question marks or deny all of them outright.
This walkthrough may help you modify my formula to suit your data accuracy requirements.
Purpose of Restrict Entering Special Characters in Google Sheets
In your daily digital life, so many times, you may have come across the term special characters. They are non-alphanumeric characters.
Unlike alphabetic and numeric (alphanumeric) characters, some special characters may have multiple uses in functions like Query, Regexmatch, Regexreplace, and Regexextract.
To prevent problems, you may want to use a backslash ( \ ) to escape some special characters when using them in the said functions.
For example, to match “DRG152 (Rev 2)” you can use =regexmatch(D8,"DRG152 \(Rev 2\)"). In this, I’ve escaped the open and close parenthesis using a \.
For the smooth functioning of the above functions, sometimes you may want users not to enter special characters in cells.
Let’s see how to restrict users from entering special characters in a cell or cell range by setting up a Data validation rule in Google Sheets.
Setting Up a Data Validation Rule in Google Sheets
To set up Data validation, please follow the below steps.
Select the cell or cell range where you want to apply the Data validation rule. For example, select A1:A by clicking on column label A.
Go to the menu Data > Data Validation > Add rule > Criteria > Custom formula is.
As per our example, the settings up to this point will be as follows.
We will insert a custom formula to restrict entering special characters in the Data validation formula field under “Custom formula is.”
Regexmatch in Data Validation to Restrict Users from Entering Special Characters
Here is our custom Regexmatch formula to enter into the above formula field in Data validation.
=REGEXMATCH(A1&"","^[a-zA-Z0-9]*$")Please note one thing. We want to restrict the entry of all special characters in column A. But, in the formula, we only require to specify the topmost cell in the range, here A1.
The above formula can restrict users from entering all special characters in the range A1:A in Google Sheets.
In other words, the formula above would only allow people to enter upper-case and lower-case letters and numbers.
Does the above rule allow space characters?
Nope! In that case, modify it as below.
=REGEXMATCH(A1&"","^[a-zA-Z0-9\s]*$")I want to deny people from entering only specific special characters in Google Sheets. Ho to do that?
See the below formula.
=NOT(REGEXMATCH(A1&"","[\&\$\%\#\@\!]"))It will only restrict the characters given within the square brackets, i.e., &,$,%,#,@,!.
That’s all. Thanks for the stay. Enjoy!
Resources:
