How to only allow alphanumeric characters entry in Excel?
For some special purposes, you may only allow the alphanumeric or numeric characters entry in a column, how can you do? In this article is going to introduce the ways to solve this problem in Excel.
Only allow alphanumeric characters entry with Data Validation
Only allow numeric characters entry with Data Validation
Only allow text entry with Data Validation
Not allow special characters entried with Kutools for Excel
Remove all except alphanumeric character from string with Kutools for Excel
Only allow alphanumeric characters entry with Data Validation
For only allowing alphanumeric characters entry in a column, you can follow below steps to handle it.
1. Select a column by clicking at the column header, for instance, column A, and click Data > Data Validation > Data Validation. See screenshot:
2. Then in the Data Validation dialog, under Settings tab, and select Custom from the Allow dropdown list,, and type this formula =ISNUMBER(SUMPRODUCT(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"))) into the Formula textbox. See screenshot:
Tip: In this formula, A1 is the first cell of your selected column, you can change it to you need.
3. Click OK, and now in column A, you only can type alphanumeric characters in it. If you enter non-alphanumeric characters into the cell, there will pop out a warning dialog.
Tip: If you only type * or ~ into the cells in column A, the warning dialog won’t display.
Only allow numeric characters entry with Data Validation
If you only allow numeric characters entry in a column, you can do as these:
1. Select the column you want to limit the entry, and click Data > Data Validation > Data Validation.
2. Then in the Data Validation dialog, select Custom from Allow dropdown list, and type this formula =ISNUMBER(B1) in the Formula textbox , and B1 is the first cell of your selected column. See screenshot:
3. Click OK, and then the selected column only allowed entry numeric characters.
Only allow text entry with Data Validation
To allow text entry in a column only, you can use a formula too.
1. Select the whole column you want to restrict, and click Data > Data Validation > Data Validation.
2. Select Custom from Allow list, and type this formula =ISTEXT(C1) (C1 is the first cell of the selected column) into Formula textbox. See screenshot:
3. Click OK to finish it.
Not allow special characters entried with Kutools for Excel
Prevent special character enteringActually, you can apply Kutools for Excel’s Prevent Typing utility to prevent special characters entering and only allow such as alphanumeric characters typing.
| Kutools for Excel, with more than 300 handy functions, makes your jobs more easier. |
After free installing Kutools for Excel, please do as below:
1. Select the cells you want to prevent special character typing, click Kutools > Prevent Typing > Prevent Typing. See screenshot:
2. In the Prevent Typing dialog, check Prevent type in special characters option, click Ok, and then there are two dialogs pops out to remind you something, click Yes > OK to continue the operation.
Then in the selected cells, the special characters are not allowed to typed.
Prevent Special Characters Entering
Remove all except alphanumeric character from string with Kutools for Excel
In some cases, you may want to remove all except alphanumeric characters from a mixed string, Kutools for Excel’s Remove Characters can do you a favor.
After free installing Kutools for Excel, please do as below:
1. Select the strings and click Kutools > Text > Remove Characters. See screenshot:
2. Then in the popping dialog, check Non-alphanumeric options, and you can preview the removing results from Preview pane. See screenshot:
3. Click Ok or Apply to execute the operation, and all the alphanumeric characters are deleted.
Remove Non-alphanumeric Characters
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
Supports Office/Excel 2007-2021 and 365 | Available in 44 Languages | Easy to Uninstall Completely
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need Is Just A Click Away...
Supercharge Your Excel Skills: Experience Efficiency Like Never Before with Kutools for Excel (Full-Featured 30-Day Free Trial)
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! (Full-Featured 30-Day Free Trial)
