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.

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

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.

export each sheet to csv2

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

 

You might also like:

Comments

So empty here ... leave a comment!

Leave a Reply

Your email address will not be published. Required fields are marked *

Sidebar