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.
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.
Step 2: Press ENTER and Drag the Fill Handle to come up with results.
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).
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.
Here, the formula defines all the arguments as it does in Step 1 of this method.
Click on the Input Message section.
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.
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.
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.
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.
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.
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.
Step 3: Hit ENTER and Drag the Fill Handle to bring up all the alphanumeric status for Employee’s ID.
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.
The arguments in the formula define the same logic as they do in earlier Step 2.
Step 5: Go to the worksheet and Place the cursor in any cell within the range (B3:B11), the Input Message appears.
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).
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.
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.
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))
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.
You can alter the string length and format according to your data type.
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.