How to Remove All Non-Alphanumeric Characters in Excel

,

This post will guide you how to remove all non-alphanumeric characters from a text string in a given cell in Excel. How do I remove non-alphanumeric characters from a string using User Defined Function or VBA Macro in Excel 2013/2016.

1. Remove One Non-Alphanumeric Character using SUBSTITUTE Function

If you want to remove a specific type of non-alphanumeric character from a cell in Excel, you can use the SUBSTITUTE function.

Here’s an example formula that will remove all hyphen characters (-) from cell A1:

=SUBSTITUTE(A1,"-","")

You can modify this formula to remove other specific non-alphanumeric characters as well. Just replace the “-” in the formula with the character you want to remove.

2. Remove All Non-Alphanumeric Characters with User Defined Function

Assuming that you have a list of data in range A1:A3 which contain text string values, and you only want to remove all non-alphanumeric characters from those cells and keep others alphanumeric characters. How to do it?

You need to write down an Excel VBA Macro to achieve the result of removing all non-alphanumeric characters quickly. Just do the following steps:

Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

Step2: then the “Visual Basic Editor” window will appear.

Step3: click “Insert” ->”Module” to create a new module.

convert column number to letter3

Step4: paste the below VBA code into the code window. Then clicking “Save” button.

remove all non-alphanumeric character1
Function RemoveNonAlpha(str As String) As String
    Dim ch, bytes() As Byte: bytes = str
    For Each ch In bytes
        If Chr(ch) Like "[A-Z.a-z 0-9]" Then RemoveNonAlpha = RemoveNonAlpha & Chr(ch)
    Next ch
End Function

Step5:  back to the current worksheet, then type the following formula in a blank cell, and then press Enter key. And drag the AutoFill Handle over to other cells.

=RemoveNonAlpha(A1)

Step6: let’s see the last result:

remove all non-alphanumeric character2

3. Remove All Non-Alphanumeric Characters with VBA Macro

You can also use another VBA macro to achieve the same result of removing all non-alphanumeric characters in Excel.  Just do the following steps:

Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1
Step2: then the “Visual Basic Editor” window will appear.

Step3: click “Insert” ->”Module” to create a new module.

Step4: paste the below VBA code  into the code window. Then clicking “Save” button.

remove all non-alphanumeric character3
Function RemoveNonAlpha(str As String) As String
    Dim ch, bytes() As Byte: bytes = str
    For Each ch In bytes
        If Chr(ch) Like "[A-Z.a-z 0-9]" Then RemoveNonAlpha = RemoveNonAlpha & Chr(ch)
    Next ch
End Function
Sub RemoveNonAlphaMacro()
    Dim rng As Range
    Set MyRange = Application.Selection
    Set MyRange = Application.InputBox("Select One Range:", "RemoveNonAlphaMacro", MyRange.Address, Type:=8)
    For Each rng In MyRange
        rng.Value = RemoveNonAlpha(rng.Value)
    Next
End Sub

Step5: back to the current worksheet, then run the above excel macro. Click Run button.

remove all non-alphanumeric character4

Step6: select one range which contain non-alphanumeric characters that you want to remove. Click Ok button.

remove all non-alphanumeric character5

Step7: you would see that all non-alphanumeric characters have been removed from the selected range of cells.

remove all non-alphanumeric character6

4. Conclusion

There are multiple ways to remove non-alphanumeric characters from cells in Excel, depending on whether you want to remove all non-alphanumeric characters or just a specific type of non-alphanumeric character.

Leave a Reply