How to only allow alphanumeric characters entry in Excel?
In many work scenarios, you may need to restrict data entry so that users can only input alphanumeric or numeric characters in a column, such as for product codes, employee IDs, registration numbers, or other fields requiring standardized formatting. Allowing only specific types of characters can help prevent errors, ensure data consistency, and simplify downstream processing. This article introduces multiple practical solutions to help you efficiently limit entries to your desired character types in Excel, including built-in options and Kutools for Excel features.
Only allow alphanumeric characters entry with Data Validation
Only allow numeric characters entry with Data Validation
Only allow text entry with Data Validation
Do not allow special characters entered with Kutools for Excel
Remove all except alphanumeric characters from a string with Kutools for Excel
Only allow alphanumeric characters entry with Data Validation
If you need to force users to enter only alphanumeric characters (letters and numbers) in a specific column, such as for serial numbers, standardized codes, or ID fields, Data Validation provides a straightforward solution suitable for most workbook management needs.
1. Select the entire target column by clicking its column header (for example, column A), then go to Data > Data Validation > Data Validation. Refer to the screenshot below:
2. In the Data Validation dialog box, under the Settings tab, set the Allow dropdown to Custom. Enter the following formula in the Formula input box:
=ISNUMBER(SUMPRODUCT(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ")))
Tip: Use your target column’s top-most cell in the formula (e.g., change A1 if needed). The formula works by checking each character in the cell to ensure it is a number or letter; if any character cannot be found in the allowed set, the validation fails and the input is rejected.
3. Click OK to confirm. Now, only alphanumeric character entries are permitted in column A. Entering non-alphanumeric characters will produce a warning dialog and block the input.
Note: For certain special character inputs such as * or ~, the warning dialog may not trigger due to Excel’s internal handling of wildcard and escape characters. Test a few sample entries to confirm the settings are working as expected.
This solution ensures that your data entry stays clean and consistent in real-time, but it does not retroactively clean existing data.
Unlock Excel Magic with Kutools AI
- Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
- Custom Formulas: Generate tailored formulas to streamline your workflows.
- VBA Coding: Write and implement VBA code effortlessly.
- Formula Interpretation: Understand complex formulas with ease.
- Text Translation: Break language barriers within your spreadsheets.
Only allow numeric characters entry with Data Validation
For columns where only numeric entries (digits) are allowed, such as for phone numbers, payment references, or calculation fields, you can also use a custom Data Validation formula:
1. Select the column you want to restrict, then navigate to Data > Data Validation > Data Validation.
2. In the Data Validation dialog, set Allow to Custom, then enter:
=ISNUMBER(B1)
Tip: Replace B1 with the top cell of your target column. This formula ensures that any input must be a number, blocking text and special character entries.
3. Click OK. The selected column now only accepts numeric character entries, improving the reliability of calculations and lookups using this data.
Only allow text entry with Data Validation
If your column should only contain textual information (no numbers or formulas), Data Validation can restrict entry accordingly. This is ideal for columns with names, categories, or descriptions.
1. Select the relevant column, then choose Data > Data Validation > Data Validation.
2. In the dialog, set Allow to Custom, and enter this formula:
=ISTEXT(C1)
Tip: Change C1 to match your selected column's leading cell. This forces the cell input to be text and excludes pure numbers and errors.
3. Click OK to enable the restriction. Later, if anyone tries to type a number or non-text entry, Excel will block and warn them.
Do not allow special characters entered with Kutools for Excel
Besides Excel’s built-in Data Validation, you can use Kutools for Excel’s Prevent Typing utility to more flexibly restrict special character entry across different data types—including alphanumeric fields, text, mixed formats, and more. This method is particularly useful for worksheets shared by multiple users.
After freely installing Kutools for Excel, follow these steps:
1. Highlight the cells or column you wish to restrict, then go to Kutools > Prevent Typing > Prevent Typing. See screenshot:
2. In the Prevent Typing dialog, enable Prevent type in special characters, then click Ok. Two reminder dialogs may appear—press Yes and then OK to finish setup.
Now, the selected cells will prohibit entry of special characters, ensuring cleaner input for tasks like importing to databases or automated processing.
Note: Kutools offers customization options for blocking not just special characters but also specifying allowed/disallowed input types—making it useful for advanced validation requirements and bulk control over large sheets.
Prevent Special Characters Entering
Remove all except alphanumeric characters from a string with Kutools for Excel
There may be scenarios where your dataset already contains undesired special characters or non-alphanumeric data mixed with the information you wish to keep. Kutools for Excel’s Remove Characters feature allows you to quickly clean up data in bulk, retaining only letters and numbers in selected ranges.
After freely installing Kutools for Excel, do as follows:
1. Select the cells containing the strings you wish to clean, then choose Kutools > Text > Remove Characters. See screenshot:
2. In the Remove Characters dialog box, check the Non-alphanumeric option. The Preview panel will show the removal results, letting you check before confirming. See screenshot:
3. Click Ok or Apply to execute, and all non-alphanumeric characters from the selected strings will be deleted, resulting in a clean dataset suitable for further analysis or export.
Note: This approach is useful for cleaning up imported data or correcting historical entries, but it does not restrict future input—the original cell content is simply modified to meet your criteria.
Relative Articles:
- How to remove empty sheets from a workbooks?
- How to allow yes or no entry in Excel?
- How to remove all duplicates but keep only one in Excel?
- How to remove first/last n characters from a cell/string in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in