How to Perform Data Validation for Alphanumeric Only in Excel

Method 1 – Using a Custom Formula to Data Validate Any Alphanumeric

STEPS:

Use the following formula in the C5 cell.

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

Press ENTER.

Inserting Formula for Finding Alphanumeric Status

In the formula, the MID function generates an array from the entered text in cell B3. The SEARCH function finds a text from “0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ” characters passing positions to the SUMPRODUCT function as Value. The ISNUMBER function returns TRUE or FALSE depending on whether the cell references are alphanumeric.

Drag down the formula using the Fill Handle to generate results.

We have alphanumeric characters in the Employee ID column.
The custom formula returns TRUE for each entry.

Use the custom formula in the Data Validation feature to achieve what you initially desired.
Select the Employee ID column.
Go to Data >> Data Tools >> Data Validation >> Data Validation.

The Data Validation dialog box opens.
In the Settings tab, choose Custom from the drop-down menu of the Allow field.
Insert the following formula in the Formula box.

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

The formula is the same as the one we used to find the Alphanumeric Status.

Click on the Input Message section.
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 the users select any Data Validated cells, this preset Input Message appears.

Click on the Error Alert tab.
In the Error Alert section, choose to Stop from the Style box.
Type a Title (i.e., Input Error) and Input Message (i.e., Please Enter Alphanumeric Only). Show whenever users input characters except for alphanumerics.
Press OK.

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.

Type any Non-alphanumeric in the B5 cell, and you’ll encounter an Input Error Message.
We have successfully applied the custom formula.
Apply the custom formula in any cell or a range of cells.

Method 2 – Applying Data Validation for Specific Alphanumerics Only

STEPS:

Insert specific alphanumeric characters in the D column.
Give them a Defined Name (i.e., Characters) using the Name Box.

Type Employee IDs consisting of allowed alphanumerics.
Use this formula in the adjacent cell (i.e., C5).

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

Press ENTER to observe the result.

The MID function creates an array using Cell Reference (i.e., B5) the MATCH function matches the array to characters (works as a lookup_array; i.e., allowed Alphanumeric). 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.

Drag down the formula using the Fill Handle option to bring up all the alphanumeric statuses for the Employee’s ID.

See the Alphanumeric Status of the Employee IDs.

Select the B5:B13 range.
Go to Data > > Data Tools >> Data Validation >> Data Validation.

The Data Validation window will open up.
In the newly opened window, go to the Settings tab.
Select Custom in the Allow field.
Write down the following formula in the Formula field.

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

This formula is the same as we used before to determine the status.

Click the Input Message section.
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.
Whenever users place their cursors in any Data Validated cells, this preset Input Message appears.

Click on the Error Alert tab.
In the Error Alert section, choose to Stop from the Style box.
Type a Title (i.e., Input Error) and Input Message (i.e., Please Enter Alphanumeric Only).
Press OK.

Return to the Excel Sheet.
When you input any characters except alphanumerics, the Input Error message window will appear.

Method 3 – Incorporating Data Validation of Fixed Length and Format Alphanumeric Only

STEPS:

Enter the Employee IDs maintaining the length (i.e.,10) and format (First 3 letters and the rest 7 numeric).
Insert the following formula in the D6 cell.

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

Press ENTER to watch the status.

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

Drag the Fill Handle down.

The Alphanumeric Status of the Employee IDs are TRUE.

Select the B6:B14 range.
Go to Data > > Data Tools >> Data Validation >> Data Validation.

The Data Validation window will open up.
Go to the Settings tab.
Select Custom in the Allow field.
Insert the following formula in the Formula field.

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

Insert the entries in the Input Message and Error Alert tab.

Return to the Excel Sheet.
Place the cursor in any cell within the Range (i.e., B6:B14)
You’ll see an Input Message window.
Try to enter any non-alphanumeric characters (i.e.,!). You’ll encounter an Input Error.

How to Use Data Validation for Specific Text Only in Excel

STEPS:

Select the Allowed Alphanumeric column and define it as Characters.
We wrote the ID with the combination of any 8 given letters in the Characters column.

Use the following formula in the D5 cell:

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

Press OK to observe the result.

Drag down the formula using the Fill Handle option.

See the Alphanumeric Status of the Employee ID.
IDs have 8 allowed letters, and the status is TRUE.

Select the B5:B13 range.
Go to Data >> Data Tools >> Data Validation >> Data Validation.

See the Data Validation window.
In the Settings tab, select Custom from the drop-down menu of the Allow field.
In the Formula field, use the following formula:

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

Insert the entries in the Input Message and Error Alert tab.

Return to the worksheet and place the cursor in any cell within the B5:B13 range, and the Input Message appears.
Try to input any text except allowed strings, and the Input Error message window will appear.