Excel Data Validation Alphanumeric Only (Using Custom Formula)

Alphanumeric characters are a mix of alphabetical and numerical characters. There are 36 (A-Z and 0-9; case insensitive) or 62 (A-Z, a-z and 0-9; case sensitive) alphanumeric characters. In this article, we’ll show Data Validation using custom formulas alphanumeric only.


Dataset for Download


Custom Formula to Excel Data Validate Alphanumeric Only

Method 1: Custom Formula to Data Validate Any Alphanumeric

We want to restrict a column from entering any characters except alphanumeric. In this case, we want alphanumerics only to be input in column Employee ID (i.e., column B). To achieve this, we have to use Excel’s Data Validation feature to apply a custom formula in those cells. This custom formula restricts characters except alphanumerics to be input in the column and also shows input types while the cursor sits on restricted cells.

To ensure a custom formula works, we have to check its workability by applying it to the worksheet. We have Employee ID in alphanumerics, we check whether our custom formula returns TRUE or FALSE assessing Employee IDs.

Step 1: Paste the following formula in any adjacent cell (i.e., C3).

=ISNUMBER(SUMPRODUCT(SEARCH(MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1),"0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ")))

In the formula,

The MID function generates an array from the entered text in cell B3. Then the SEARCH function finds text from “0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ” characters passing positions to the SUMPRODUCT function as Value.

At last, the ISNUMBER function returns TRUE or FALSE depending on the cell references to be alphanumeric or not.

Custom formula-Excel Data Validation Alphanumeric Only

Step 2: Press ENTER and Drag the Fill Handle to come up with results.

Alphanumeric Status

As you can see, we have alphanumeric characters in the Employee ID column and the custom formula returns TRUE for each entry. Now, you can use the custom formula in the Data Validation feature to achieve what you desired at the beginning.

Step 3: Select the Employee ID column (i.e., Column B) by clicking the column header (i.e., B). Then Go to Data Tab > Select Data Validation (from Data Tools section) > Choose Data Validation (from the Options).

Data Validation

Step 4: The Data Validation dialog box opens up. In the Settings section, Choose Custom from the Allow drop-down menu box.

Paste the following Custom formula inside the Formula box.

=ISNUMBER(SUMPRODUCT(SEARCH(MID(B1,ROW(INDIRECT("1:"&LEN(B1))),1),"0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ")))

Here, the formula defines all the arguments as it does in Step 1 of this method.

Click on the Input Message section.

Formula insertion

 Step 5: In the Input Message section, Type any Title (i.e., Input Type) and Input Message (i.e., Please Enter Alphanumeric Only) to guide the users. When even users place their cursors in any Data Validated cells this preset Input Message appears.

Click on Error Alert.

Input message

Step 6: In the Error Alert section, Choose Stop from the Style box. Type a Title (i.e., Input Error) and Input Message (i.e., Please Enter Alphanumeric Only), you want to show whenever users input characters except for alphanumerics.

Click OK.

Error alert

Now, Place the cursor in any cell within column B. You’ll see a message box saying Input DataType Must be in Alphanumerics similar to the image below.

Cursor placing

In case you want to test the formula, just Type any Non-alphanumeric in any cell (i.e., B3), you’ll encounter an Input Error Message as we set earlier in Step 6.

Input Error

You can apply the custom formula in any cell or a range of cells.


Method 2: Data Validation of Specific Alphanumerics Only

We want to allow users to input only specific alphanumerics in the worksheet. Let’s say we want the Employee IDs to be built with specific alphanumeric characters.

Step 1: Insert specific alphanumeric characters in a column (i.e., Column D). After that, Give them a Defined Name (i.e., characters) using the Name Box.

Specific alphanumeric-Excel Data Validation Alphanumeric Only

Step 2: Type Employee IDs consisting of allowed alphanumerics. Then to test the custom formula, Paste the custom formula to any adjacent cell (i.e., C3).

=COUNT(MATCH(MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1),characters&"",0))=LEN(B3)

Inside the formula,

The MID function creates an array using Cell Reference (i.e., B3) then the MATCH function matches the array to characters (works as a lookup_array; i.e., Allowed Alphanumeric).

At last, the COUNT function declares that the Cell Reference is equal to the length of cell B3. The overall function returns it as TRUE or FALSE.

Alphanumeric status

Step 3: Hit ENTER and Drag the Fill Handle to bring up all the alphanumeric status for Employee’s ID.

Fill handle

 Step 4: Select a Range (i.e., B3:B11) and Repeat Step 3 to 6 of Method 1 to Data Validate alphanumerics only replacing the custom formula in Step 4 with the following custom formula.

=COUNT(MATCH(MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1),characters&"",0))=LEN(B3)

The arguments in the formula define the same logic as they do in earlier Step 2.

Click OK.

Formula Insertion

Step 5: Go to the worksheet and Place the cursor in any cell within the range (B3:B11), the Input Message appears.

Input Error-Excel Data Validation Alphanumeric Only

Try to Input any characters except alphanumerics, the Input Error message window comes up.


Method 3: Data Validation of Fixed Length and Format Alphanumeric Only

In this method, we want to take the custom formula a step further by fixing the length and format of entries to apply Data Validation.

In this case, we want case-sensitive alphanumerics to be present in Employee IDs with a fixed-length (i.e.,10) and format. The first 3 characters in Employee IDs should be alphabetic and the rest 7 characters should be numeric.

Step 1: Enter the Employee IDs maintaining the length (i.e.,10) and format (First 3 letters and rest 7 numeric). Write down the following formula in any adjacent cell (i.e., C4).

=AND(LEN(B4)=10,LEN(SUBSTITUTE(B4," ","")<=3),ISNUMBER(1*RIGHT(B4,7)))

Specific length and format-Excel Data Validation Alphanumeric Only

Step 2: Press ENTER and Drag the Fill Handle to check whether the entries satisfy what we desire. TRUE declares the entries satisfy the requirements and FALSE otherwise.

Fill handle

Step 3: Select a Range (i.e., B4:B12) afterward Repeat Step 3 to 6 of Method 1 replacing the custom formula with the following custom formula in Step 4 to Data Validate alphanumeric only.

=AND(LEN(B4)=10,LEN(SUBSTITUTE(B4," ","")<=3),ISNUMBER(1*RIGHT(B4,7)))

Within the formula,

The AND function declares three logical argument; first one for the length (i.e.,LEN(B4)=10), second one is for first three letters (i.e.,LEN(SUBSTITUTE(B4,” “,””)<=3), and last one is for last seven numbers (i.e.,ISNUMBER(1*RIGHT(B4,7))

Click OK.

Formula insertion

Step 4: Place the cursor in any cell within the Range (i.e., B4:B12), you’ll see an Input Message window. Try to enter any non-alphanumeric characters(i.e.,!), you’ll encounter an Input Error as you set earlier Steps.

Input Error-Excel Data Validation Alphanumeric Only

You can alter the string length and format according to your data type.


Conclusion

In this article, we saw Data Validation for alphanumerics only using three custom formulas. We develop three different custom formulas to comply with three different criteria. The first criterion is Data Validate any alphanumeric in a column, the second criterion is Data Validate only specific alphanumerics, and the last one is maintaining a string length Excel Data Validate a specific format for desired data types.


Further Readings

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo