In cases like item codes, employee codes or some other codes, you may only want to allow the entry of alphanumerics in Google Sheets. This article describes how to only permit alphanumerics’ entry using data validation in Google Sheets.

To control the entry of values in a cell or a range of cells, you can use the Data menu Data validation command. The same Data validation we can use to only allow entering alphanumerics in a column in Google Sheets.

Alphanumerics are a combination of letters and numbers. In Google Sheets, these combinations must only contain characters from;

• 26 letters (A-Z) + 10 numbers (0-9) or
• 26 letters (a-z) + 10 numbers (0-9)

…in a case sensitive alphanumeric data validation.

The total number of characters must be from the 52 letters (A-Za-z) + 10 numbers (0-9) combination in a case insensitive alphanumeric data validation in Sheets.

In this tutorial, I have included how to only allow the entry of both the case sensitive as well as the case insensitive alphanumerics in a column.

In addition to this, you can also learn how to allow special characters while validating alphanumeric characters.

I am going to apply my data validation to the range A1:A10. Please note that you can either select any single cell, like A1 or a range like A1:A10.

To only allow the entry of alphanumerics in Google Sheets, I am using custom rules based on Regexmatch.

Regexmatch to Allow Only the Entry of Alphabets in Google Sheets

To only allow text entry in the range A1:A10 follow the below steps.

Allow The Entry of Case Insensitive Alphabets in Data Validation

1. Highlight to select A1:A10.
2. Go to Data > Data validation.
3. Select the “Custom formula is” criteria and enter the following Regexmatch formula.

Formula # 1:

=regexmatch(A1,"^[a-zA-Z]*$")

With the above rule in Google Sheets Data validation, you will only be able to enter the strings containing the letters A-Z, a-z or both mixed in a column.

I have depicted the same below. See column A and also the Data validation settings box.

Allow The Entry of Case Sensitive Alphabets in Data Validation

How to only allow the letters A-Z or a-z using Regexmatch in Data validation in Google Sheets?

Data Validation a-z Only

The following Regexmatch will only allow the entry of lowercase/small case letters in the selected range A1:A10.

Formula # 2:

=regexmatch(A1,"^[a-z]*$")

Data Validation A-Z Only

To allow only the entry or capital (uppercase) letters using Data validation, use the Regexmatch Formula # 3 below in Google Sheets.

Formula # 3:

=regexmatch(A1,"^[A-Z]*$")

Allow Only The Entry of Numbers in Data Validation

By going through the above two formulas, you may think you can quickly tune the regular expression to only allow the entry of numbers in a cell/range. You are partially right!

But do note that replacing A-Z or a-z to 0-9 won’t work. In addition to this change in the regular expression, you should use the To_text function. To_text formats numbers to string like adding ' prefix the number in question.

The Regexmatch to only allow the entry of numbers will be as per the Formula # 4 below.

Formula # 4:

=regexmatch(to_text(A1),"^[0-9]*$")

Regexmatch in Data Validation to Allow Only the Entry of Alphanumerics in Google Sheets

I have given the formulas to only allow the entry of numbers or alphabets in Google Sheets. Now using the above formulas, we can code a rule to only allow the entry of alphanumerics in Google Sheets.

Here also you can think about bringing case sensitivity in the data validation. Here are the required formulas.

Data Validation Rule to Allow the Entry of Case Insensitive Alphanumerics in a Column

Formula # 5:

=and(regexmatch(regexextract(A1,"[A-Za-z]+"),"^[a-zA-Z]*$"),regexmatch(regexextract(A1,"[0-9]+"),"^[0-9]*$"),len(regexreplace(A1,"[a-zA-Z0-9]+",""))=0)=true

Formula Explanation

Actually, the above Regex that validates alphanumerics only in Google Sheets is a combination of three formulas. Here is the generic formula version of above Formula # 5.

=and(Formula # 1,Formula # 4,len(regexreplace(A1,"[a-zA-Z0-9]+",""))=0)=true

You have already learned Formula # 1 and Formula # 4 above. But here there is a slight difference in that formulas use. What are those changes?

In earlier Formula # 1 and Formula # 4, the cell references are the cell A1. But here the cell reference A1 in Formula # 1 is replaced by regexextract(A1,"[A-Za-z]+") to extract the alphabets from the string.

Similarly, A1 in Formula # 4 is replaced by regexextract(A1,"[0-9]+") to extract numbers.

In addition to the above two formulas, there is one new formula that extracts special characters if any and counts it’s length.

len(regexreplace(A1,"[a-zA-Z0-9]+",""))=0)

If the length of special character is 0, there are no special characters in the string.

That means if you want to include a dash, underscore or some other special characters with the alphanumerics, simply remove the above last formula from the data validation rule! It would be as follows.

Formula to Validate Alphanmuerics + Special Characters in Google Sheets:

Formula # 6:

=and(regexmatch(regexextract(A1,"[A-Za-z]+"),"^[a-zA-Z]*$"),regexmatch(regexextract(A1,"[0-9]+"),"^[0-9]*$"))=true

Data Validation to Permit the Entry of Case Sensitive Alphanumerics in a Column

Case Sensitive Formula that Does Not Allow Special Characters with Alphanumerics

Formula # 7: Uppercase Letters.

=and(regexmatch(regexextract(A1,"[A-Z]+"),"^[A-Z]*$"),regexmatch(regexextract(A1,"[0-9]+"),"^[0-9]*$"),len(regexreplace(A1,"[A-Z0-9]+",""))=0)=true

Formula # 8: Lowercase Letters.

=and(regexmatch(regexextract(A1,"[a-z]+"),"^[a-z]*$"),regexmatch(regexextract(A1,"[0-9]+"),"^[0-9]*$"),len(regexreplace(A1,"[a-z0-9]+",""))=0)=true

In the case of the above two formulas, I don’t want to go for formula explanation. You can check the Formula # 5 explanation which is applicable here also.

Case Sensitive Formula that Does Allow Special Characters with Alphanumerics

Here remove the third formula, the formula that starts with len, from formula 7 and 8.

That’s all. Enjoy!