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.

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.

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:

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.

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.

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.

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

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

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.