How to Restrict Entering Special Characters in Google Sheets Using Regex

0
205
Restrict Entering Special Characters in Google Sheets

With the help of Data Validation and REGEXMATCH, you can restrict entering special characters in Google Sheets. In this tutorial, I am using a REGEXMATCH custom formula to deny the entry of any special characters in Google Doc Spreadsheets. Without Regex, I may need to use a long text formula to achieve the same result.

Regex Custom Formula to Restrict Entering Special Characters in Google Sheets

Here I am ready to explain you how to set data validation rule with Regex to deny entering special characters like @!$& etc. Search on this site to find some in depth tutorials related to data validation. Feel free to use our Search Field on the top.

What is Special Characters in Google Sheets

In our daily digital life, So many times we have come across the term special characters. You must use special characters to set up strong passwords and I know you are possibly using such character combinations to tighten the security of your online banking accounts.

Any way, I am not going to provide you the long list of special characters. Still for your information let me name few of them. Special characters include space, exclamation mark, dollar, ampersand, comma, hyphen, semicolon, less than, greater than, numbers etc. But here in this tutorial, I am going to white list numbers when it is using together with text.

I have detailed the steps to block people from entering special characters in Google Sheets under two sections below. Let’s begin.

1. How to Set Data Validation in Google Sheets

This section is purely for newbies. You can find data validation under the menu Data. To set data validation follow the below steps.

1. Select the range where you want to apply the data validation rule.

2. Go to the menu Data > Data Validation.

3. For our example purpose, below is the suitable settings. Here we will apply the custom formula which I am going to provide in the blank field against “Criteria”. Please note that the “Cell range:” is based on my selection. It would be different for you.

How to Set Data Validation in Google Sheets

2. REGEXMATCH to Match only Special Characters in Google Sheets

Here is our custom Regexmatch formula to apply to the above blank field in data validation.

=ArrayFormula(REGEXMATCH(Sheet4!D1:D10,”^[a-zA-Z0-9]*$”))

Important: You can copy and paste this formula but you should re type the double quotes. Also change the range in the formula based on your sheet range.

Conclusion:

The above formula can restrict entering Special Characters in Google Sheets. In other words, the formula above would only allow people to enter upper or lower case letters and numbers with text. You can’t enter the number independently. Check ourĀ Regex tutorial to tweak the above formula suitable for your requirement.

LEAVE A REPLY

Please enter your comment!
Please enter your name here