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.

• Press ENTER.

• 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.

• 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).

• Press ENTER to observe the result.

• 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.

• 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.

• Press ENTER to watch the status.

• 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.

• 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:

• 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:

• 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.

Download the Practice Workbook

Related Articles

• Excel Data Validation for Date Format
• How to Use Data Validation in Excel with Color
• How to Circle Invalid Data in Excel
• How to Create Data Validation with Checkbox Control in Excel
• [Fixed] Data Validation Not Working for Copy Paste in Excel
• Excel Data Validation Greyed Out

<< Go Back to Excel Custom Data Validation | Data Validation in Excel | Learn Excel