How to prevent special characters from being entered in Excel?
In some worksheets, you may want cells to accept only letters and numbers while preventing special characters such as @, #, $, %, and &. This article introduces three ways to restrict special characters when values are entered in Excel.
- Prevent special characters with Data Validation
- Prevent special characters with VBA code
- Prevent special characters with Kutools for Excel
- Video: Prevent special characters with Kutools for Excel
- Frequently asked questions
Comparison of methods for preventing special characters
| Method | How it works | Considerations |
|---|---|---|
| Data Validation | Uses a custom formula to allow only letters and numbers. | Built into Excel, but pasted data can bypass Data Validation. |
| VBA code | Automatically clears invalid entries from a specified range. | Requires a macro-enabled workbook and enabled macros. |
| Kutools for Excel | Blocks special characters in the selected range through the Prevent Typing dialog. | No formula or VBA setup is required. |
Prevent special characters with Data Validation
Excel's Data Validation feature can restrict the selected cells to letters and numbers.
- Select the range in which you want to prevent special characters.
- Click Data > Data Validation > Data Validation.
- In the Data Validation dialog box, open the Settings tab and select Custom from the Allow list. Enter the following formula in the Formula box:
=IF(A1="",TRUE,SUMPRODUCT(--ISNUMBER(FIND(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789")))=LEN(A1))📝 Note:
A1 must refer to the first cell in the selected range. Change it to match your actual selection. This formula allows only English letters and numbers; spaces, punctuation, and other special characters are rejected.
- Click OK. If you type a value containing a special character in the validated range, Excel displays a warning and rejects the entry.
Prevent special characters with VBA code
The following worksheet event code checks entries in a specified range and clears any value that contains a character other than a letter or number.
- Press Alt + F11 to open the Microsoft Visual Basic for Applications window.
- In the Project Explorer, double-click the worksheet on which you want to restrict entries. Paste the following code into that worksheet's code window:
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
📝 Notes:
Change A1:A100 in Private Const FCheckRgAddress As String = "A1:A100" to the range you want to monitor. Because this is worksheet event code, it must be placed in the relevant worksheet's code window, not in a standard Module. Save the workbook as a macro-enabled file.
- Save and close the Visual Basic editor. When a value containing a special character is entered in the specified range, the entry is cleared and a warning message appears.
Prevent special characters with Kutools for Excel
With the Prevent Typing feature in Kutools for Excel, you can block special characters in a selected range without creating a formula or writing VBA code.
- Select the range in which you want to prevent special characters, and click Kutools > Prevent Typing > Prevent Typing.
- In the Prevent Typing dialog box, select Prevent type in special characters.
- Click OK. A message warns that applying this feature will remove existing Data Validation rules from the selected range. Click Yes to continue, and then acknowledge the confirmation message.
- Click OK to close the dialog box. A warning now appears whenever you try to enter a special character in the selected range.
💡 Tip:
If you also need to stop duplicate values from being entered in a column, try Kutools for Excel's Prevent Duplicate feature. Download and start a free trial
Kutools for Excel - Supercharge Excel with over 300 essential tools, making your work faster and easier, and take advantage of AI features for smarter data processing and productivity. Get It Now
Video: Prevent special characters with Kutools for Excel
Frequently asked questions
Does the Data Validation formula also block spaces?
Yes. The allowed-character string contains only English letters and numbers, so spaces are treated as invalid. To allow spaces, add a space inside the allowed-character string after 0123456789 or another convenient position.
Why can special characters still appear after I paste data?
Excel Data Validation mainly controls values typed directly into cells and can be bypassed by some paste operations. Use the VBA or Kutools method if pasted entries also need to be checked or blocked.
Can I allow selected symbols such as hyphens or periods?
Yes. With the Data Validation method, add the permitted symbols to the allowed-character string. With Kutools, use the option for allowing only specified characters and include the letters, numbers, and symbols you want to permit.
Why does the VBA code not run?
Make sure the code is in the correct worksheet's code window, the workbook is saved as an .xlsm file, and macros are enabled when the workbook opens.
Can these methods allow letters from languages other than English?
The supplied formula and VBA pattern allow only the English letters A–Z and the digits 0–9. To accept other alphabets, the permitted characters or the VBA pattern must be adjusted.
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