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?
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 which contain the special characters in the specified column you have apply the option, you will get the following warning message.
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, and now, when you enter text values with some special characters in the range A1:A100, your value will be cleared at once, and you can get the following warning message.
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.
|Kutools for Excel, with more than 300 handy functions, makes your jobs more easier.|
After installing Kutools for Excel, please do as below:(Free Download Kutools for Excel Now!)
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.
You are guest
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.
- To post as a guest, your comment is unpublished.· 3 years agoWhat if i need to prevent in a single cell typing certain characters in combination with length of the text?
For example, i want to prevent the text to be between 5-16 caharacters in comination with certain character prevention? Any advice?
- To post as a guest, your comment is unpublished.· 5 years agoThe solution with Data Validation is almost perfect. But I'm able to put "*" character the such protected field. Any advice?
- To post as a guest, your comment is unpublished.· 3 years agoInstead of SEARCH function try using FIND: =ISNUMBER(SUMPRODUCT(FIND(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ")))