Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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 good idea3


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:

doc-prevent-characters-1

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).

doc-prevent-characters-1

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.

doc-prevent-characters-1


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

doc-prevent-characters-1

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.

doc-prevent-characters-1


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.

Kutools for Excel, with more than 120 handy functions, makes your jobs 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:
doc prevent characters 10

2. In the Prevent Typing dialog, check Prevent type in special characters option. See screenshot:
doc prevent characters 7

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:
doc prevent characters 8

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.
doc prevent characters 9

Tip.If you want to stop typing duplicate values in a column, please try to use the Kutools for Excel’s Prevent Duplicate as shown in the following screenshot. It’s full function without limitation in 60 days, please download and have a free trial now.

doc prevent duplicate


Related articles:

How to prevent entering values with spaces in Excel?

How to prevent duplicate entries in a column in Excel?


Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

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!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    WilY · 3 months ago
    When I use your VBA code in excel 2013 it will open up debug option and freezes
    • To post as a guest, your comment is unpublished.
      Sunny · 3 months ago
      Hi, WilY, if it pops out a dialog as below screenshot shown, just click Yes then OK to enable the code. This issue appears in Excel 2016, too.
  • To post as a guest, your comment is unpublished.
    Milan · 1 years ago
    What 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.
      Sunny · 1 years ago
      Maybe you can try the Data Validation function to limit the text length. See screenshot:
  • To post as a guest, your comment is unpublished.
    Pavel · 3 years ago
    The solution with Data Validation is almost perfect. But I'm able to put "*" character the such protected field. Any advice?
    Many thanks
    Pavel
    • To post as a guest, your comment is unpublished.
      unknown · 1 years ago
      Instead of SEARCH function try using FIND: =ISNUMBER(SUMPRODUCT(FIND(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ")))