HomeGoogle DocsSpreadsheetRestrict Entering Special Characters in Google Sheets (Data Validation)

Restrict Entering Special Characters in Google Sheets (Data Validation)

Published on

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.

Restrict Entering Special Characters: Data Validation Setting

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:

Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

LEAVE A REPLY

Please enter your comment!
Please enter your name here