How to Perform Data Validation for Alphanumeric Only in Excel

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 how to do the task of Excel Data Validation for alphanumeric only.

3 Easy Methods to Perform Data Validation for Alphanumeric Only in Excel

To demonstrate the methods, we will use the following dataset. The B column contains the Employee Id, the C column contains the Employee Name and the D column denotes if the Alphanumeric Status is OK. Here, we want to put in the B column only the Alphanumeric form. So, we will show you 3 easy methods of using custom formulas to do Data Validation for alphanumeric only. Let’s follow us to learn the methods.

1. Using Custom Formula to Data Validate Any Alphanumeric

Here, the custom formula restricts characters except alphanumerics to be input in the specific 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 when assessing Employee IDs.

STEPS:

• Firstly, type the following formula in the C5 cell.

• After typing, press ENTER to observe the result.

• After that, drag down the formula using the Fill Handle option to come up with results.

• As you can see, we have alphanumeric characters in the Employee ID column.
• Therefore, 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.
• To do so, select the Employee ID column.
• After selection, go to Data >> Data Tools >> Data Validation  >> Data Validation.

• Instantly, the Data Validation dialog box opens up.
• In the Settings tab, choose Custom from the drop-down menu of the Allow field.
• Then, write the following Custom formula inside the Formula box.

• Here, the formula is the same as we used to find out the Alphanumeric Status.

• Now, 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 even users place their cursors in any Data Validated cells this preset Input Message appears.

• After that, click on the Error Alert tab.
• In the Error Alert section, choose to Stop from the Style box.
• Then, 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.
• After that, press OK.

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

• In case you want to test the formula, just Type any Non-alphanumeric in the B5 cell, and you’ll encounter an Input Error Message.
• Therefore, we have successfully applied the custom formula.
• Similarly, you can apply the custom formula in any cell or a range of cells.

2. Applying Data Validation for Specific Alphanumerics Only

In this method, 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. Let’s follow the steps below to learn the method.

STEPS:

• Firstly, insert specific alphanumeric characters in the D column.
• After that, Give them a Defined Name (i.e., Characters) using the Name Box.

• Then, type Employee IDs consisting of allowed alphanumerics.
• After that, to test the custom formula, Paste the custom formula to any adjacent cell (i.e., C5).

• Then, press ENTER to observe the result.

• Now, drag down the formula using the Fill Handle option to bring up all the alphanumeric statuses for the Employee’s ID.

• As a result, you can see the Alphanumeric Status of the Employee Ids.

•  In the following step, select the B5:B13 range.
• After selection, go to Data > > Data Tools >> Data Validation  >> Data Validation.

• Instantly, the Data Validation window will open up.
• In the newly opened window, go to the Settings tab.
• Then, select Custom in the Allow field.
• After that, write down the following formula in the Formula field.

• Here, this formula is the same as we used before to find out the status.

• Now, 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 even users place their cursors in any Data Validated cells this preset Input Message appears.

• After that, click on the Error Alert tab.
• In the Error Alert section, choose to Stop from the Style box.
• Then, 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.
• After that, press OK.

• Finally, return to the Excel Sheet.
• Then, try to Input any characters except alphanumerics and the Input Error message window comes up.

3. Incorporating 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. Let’s follow the steps below to learn the method:

STEPS:

• Firstly, enter the Employee IDs maintaining the length (i.e.,10) and format (First 3 letters and the rest 7 numeric).
• After writing, write down the following formula in the D6 cell.

• Then, press ENTER to watch the status.

• Now, drag the Fill Handle to check whether the entries satisfy what we desire.
• Here, TRUE declares the entries satisfy the requirements and FALSE otherwise.

• As a result, you can see the Alphanumeric Status of the Employee Ids are TRUE.

•  In the following step, select the B6:B14 range.
• After selection, go to Data > > Data Tools >> Data Validation  >> Data Validation.

• Instantly, the Data Validation window will open up.
• In the newly opened window, go to the Settings tab.
• Then, select Custom in the Allow field.
• After that, write down the following formula in the Formula field.

• Now, insert the entries in the Input Message and Error Alert tab.
• Previously, we have shown the method.

• Finally, return to the Excel Sheet.
• Now, place the cursor in any cell within the Range (i.e., B6:B14)
• And, instantly, you’ll see an Input Message window.
• After that, try to enter any non-alphanumeric characters (i.e.,!), you’ll encounter an Input Error as you set earlier Steps.

How to Use Data Validation for Specific Text Only in Excel

Suppose, we want to allow users to input only specific strings in the worksheet. Let’s follow the steps to learn the method.

STEPS:

• Firstly, select the Allowed Alphanumeric column and define it as Characters.
• Here, we have allowed writing the Id with the combination of any 8 given letters in the Characters column.

• After that, write down the following formula in the D5 cell:

• Then, press OK to observe the result.

• Now, drag down the formula using the Fill Handle option.

• As a result, you can see the Alphanumeric Status of the Employee ID.
• IDs are the combination of 8 allowed letters, so the status is TRUE.

• Then, select the B5:B13 range.
• After selection, go to Data >> Data Tools >> Data Validation >> Data Validation.

• Instantly, you can see the Data Validation window.
• In the Settings tab, select Custom from the drop-down menu of the Allow field.
• Then, in the Formula field, write down the following formula:

• Now, insert the entries in the Input Message and Error Alert tab.
• Previously, we have shown the method.

• Finally, return to the worksheet and place the cursor in any cell within the B5:B13 range, and the Input Message appears.
• After that, try to Input any text except allowed strings, and the Input Error message window comes up.

Practice Section

We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.

Download Practice Workbook

To practice by yourself, download the following workbook.

Conclusion

In this article, we saw Data Validation for alphanumerics only using three custom formulas. We developed 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 Validates a specific format for desired data types. There is a practice workbook at the beginning of the article. Go ahead and give it a try. Last but not least, please use the comment section below to post any questions or make any suggestions you might have.

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