How to remove non-alphanumeric characters in Excel?
Supposing there is a range of data, such as JAMES0898#4%^{}, which contains non-alphanumeric characters in a worksheet, and now you just only want to remove the non-alphanumeric characters and keep the alphanumeric characters. For example, JAMES0898#4%^{} > JAMES0898. How do you quickly delete those non-alphanumeric characters in Excel?
- Remove non-alphanumeric characters with VBA code
- Remove non-alphanumeric characters with User Defined Function
- Remove non-alphanumeric characters with Kutools for Excel
Remove non-alphanumeric characters with VBA code
With the following short VBA code, you can remove the non-alphanumeric characters as well. You can do it as this:
1. Press Alt + F11 keys together to open the Microsoft Visual Basic for applications window.
2. Click Insert > Module, and then paste below VBA code into the Module:
VBA:Remove all non-alphanumeric characters
Sub RemoveNotAlphasNotNum()
'Updateby2014128
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
xOut = ""
For i = 1 To Len(Rng.Value)
xTemp = Mid(Rng.Value, i, 1)
If xTemp Like "[a-z.]" Or xTemp Like "[A-Z.]" Or xTemp Like "[0-9.]" Then
xStr = xTemp
Else
xStr = ""
End If
xOut = xOut & xStr
Next i
Rng.Value = xOut
Next
End Sub
3. Press F5 key or click the Run button to execute the code.
4. Then select a range you want to remove all non-alphanumeric characters in the pop-up dialog, and click the OK button.
Now all of the non-alphanumeric characters have been deleted from the selected range. See screenshot:
2 clicks to remove all non-alphanumeric characters by an amazing tool
We commonly put cursor into a cell and then remove non-alphanumeric characters one by one manually. If you are familiar with VBA or User Defined Functions, you can code it and remove all non-alphanumeric characters in bulk. But here, I will recommend an amazing tool, Remove Characters feature of Kutools for Excel, which can remove all kinds of characters with 2 clicks only!
Full Feature Free Trial
30-day!
Remove non-alphanumeric characters with User Defined Function
You can also define a custom function to remove all non-alphanumeric characters from cells in Excel.
1. Press Alt + F11 keys together to open the Microsoft Visual Basic for applications window.
2. Click Insert > Module, and then paste below code into the Module:
User Defined Function: Remove all non-alphanumeric characters
Function DeleteNonAlphaNumeric(xStr As String) As String
Dim xStrR As String
Dim xCh As String
Dim xStrMode As String
Dim xInt As Integer
xStrMode = "[A-Z.a-z 0-9]"
xStrR = ""
For xInt = 1 To Len(xStr)
xCh = Mid(xStr, xInt, 1)
If xCh Like xStrMode Then
xStrR = xStrR & xCh
End If
Next
DeleteNonAlphaNumeric = xStrR
End Function
3. Now go back to the worksheet, select a blank cell, and enter the formula =DeleteNonAlphaNumeric(B3), and then drag the AutoFill handle download to apply this formula to other cells.
Remove non-alphanumeric characters with Kutools for Excel
With above two methods, you need to know the codes, but as Excel beginners, most of us can’t use the code, so here I will give you an easy way to do it.
Kutools for Excel- Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now
If you have installed Kutools for Excel, please do it as following steps:
1. Select the range that you need to remove non-alphanumeric characters from, and click Kutools > Text > Remove Characters.
2. Then a Delete Characters dialog box will appear, only check Non-alphanumeric option, and click the Ok button.
Now all of the non-alphanumeric characters have been deleted from the text strings.
Related articles:
The Best Office Productivity Tools
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. 60-day money back guarantee.

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!
