How to prevent special characters entering in Excel?
For some cases, we just want to enter letters or numbers in cells, and prevent typing the special characters, such as @#$%& and so on. Are there any functions in Excel to prevent the special characters entering when we insert values?
Prevent special characters entering with Data Validation
Prevent special characters entering with VBA code
Prevent special characters entering with Kutools for Excel 
Prevent special characters entering with Data Validation
Excel’ Data Validation can help to allow you only to enter the alphanumeric values. Please do as follows:
1. Select a range that you want to prevent the special characters entering.
2. Then click Data > Data Validation > Data Validation, see screenshot:
3. In the Data Validation dialog box, click Settings tab, and choose Custom from the Allow drop down list, then enter this formula =ISNUMBER(SUMPRODUCT(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"))) into the Formula text box, see screenshot:
Note: A1 indicates the first cell of your selected range (from left to right).
4. Then click OK to close this dialog, and now, when you enter values containing special characters in the specified column where you applied the option, you will get the following warning message.
Prevent special characters entering with VBA code
The following VBA code also can help you to prevent the special characters while entering the text values.
1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.
2. Then choose your used worksheet from the left Project Explorer, double click it to open the Module, and then copy and paste following VBA code into the blank Module:
VBA code: prevent special characters entering in Excel
Private Const FCheckRgAddress As String = "A1:A100"
Private Sub Worksheet_Change(ByVal Target As Range)
'Update 20140905
Dim xChanged As Range
Dim xRg As Range
Dim xString As String
Dim sErrors As String
Dim xRegExp As Variant
Dim xHasErr As Boolean
Set xChanged = Application.Intersect(Range(FCheckRgAddress), Target)
If xChanged Is Nothing Then Exit Sub
Set xRegExp = CreateObject("VBScript.RegExp")
xRegExp.Global = True
xRegExp.IgnoreCase = True
xRegExp.Pattern = "[^0-9a-z]"
For Each xRg In xChanged
If xRegExp.Test(xRg.Value) Then
xHasErr = True
Application.EnableEvents = False
xRg.ClearContents
Application.EnableEvents = True
End If
Next
If xHasErr Then MsgBox "These cells had invalid entries and have been cleared:"
End Sub
Note: In the above code, you can change the range A1: A100 of Private Const FCheckRgAddress As String = "A1:A100" script to your own range that you want to prevent the special characters.
3. Then save and close this code. Now, when you enter text values with special characters in the range A1:A100, the entry will be cleared immediately, and you will receive the following warning message.
Prevent special characters entering with Kutools for Excel
Actually, if you have Kutools for Excel -- a handy and multi-functional tool, you can quickly prevent typing special characters in a selection with its Prevent Typing utility by one check.
After installing Kutools for Excel, please do as below:
1. Select a selection you want to prevent special characters typing, and click Kutools > Prevent Typing > Prevent Typing. See screenshot:
2. In the Prevent Typing dialog, check Prevent type in special characters option. See screenshot:
3. Click Ok, and a dialog pops out to remind you it will remove Data Validation if apply this utility and click Yes to go to next dialog, it will remind you the utility has been work at the section. see screenshot:
4. Click OK to close the dialog, and from now on, a warning dialog pops out when you try to enter a special character in the selection.
Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now
Demo: Prevent Special Characters Entering using Kutools for Excel
Related articles:
How to prevent entering values with spaces in Excel?
How to prevent duplicate entries in a column 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