How to Remove Non-Alphanumeric Characters in Excel (2 Methods)

Today in this article, we will demonstrate how to remove non-alphanumeric characters from cells in excel. It is a very common problem especially when you are dealing with raw data. Numbers, texts, and non-alphanumeric characters are mixed in the cells and sometimes we need to separate those texts and numbers. There are many formulas to remove non-alphanumeric characters from cells in excel using VBA programming codes and User Defined Functions. We will learn them in this assignment.


Download Practice Workbook

Download this practice book to exercise the task while you are reading this article.


2 Suitable Ways to Remove Non-Alphanumeric Characters in Excel

Imagine a situation when you receive a raw dataset to analyze and you see that numbers and texts are mixed with non-alphanumeric characters in one column. To work conveniently, you need to remove them from the string. Running VBA code or applying a user-defined function can be helpful in this matter. Here in this section, we will demonstrate those suitable methods to do that job.

Run a VBA Code to Remove Non-Alphanumeric Characters in Excel


1. Run a VBA Code to Remove Non-alphanumeric Characters

You can use the VBA macro code to remove non-alphanumeric characters from cells in excel. We will demonstrate this method below.

Step 1:

  • First, we will open our Microsoft Visual Basic for Applications Window. To do that, press Alt+11.

Run a VBA Code to Remove Non-Alphanumeric Characters in Excel

  • A new window is opened. Now click Insert and select Module to open a new module.

Run a VBA Code to Remove Non-Alphanumeric Characters in Excel

Step 2:

  • In the newly opened module, Insert the VBA code to remove non-alphanumeric characters. We have provided the code for you. You can just copy this code and use it in your worksheet. The code is,
Sub VBA_to_remove()
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "Non-alphanumeric text remove"
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

Run a VBA Code to Remove Non-Alphanumeric Characters in Excel

  • Now run the code by pressing F5. Select your range and click OK to conclude the operation.

Run a VBA Code to Remove Non-Alphanumeric Characters in Excel

  • We have successfully removed those non-alphanumeric characters from our cells.

Run a VBA Code to Remove Non-Alphanumeric Characters in Excel

Read More: How to Remove Non-numeric Characters from Cells in Excel


Similar Readings:


2. Apply a User Defined Function to Remove Non-Alphanumeric Characters

Since excel does not have any distinct function to remove non-alphanumeric characters from cells, you can make one! That’s right, you can define a custom function of your own to do your job. You need to write a VBA code to do that. We will discuss this process using the steps below. To know more about custom-made functions, Click Here!

Step 1:

  • First, go to the Microsoft Visual Basic for Applications Window by pressing Alt+F11.

Apply a User Defined Function to Remove Non-Alphanumeric Characters in Excel

  • Write down the code for your UFD. We have named our function RemoveNonNlphaNumeric. And the code to create this function is,
Function RemoveNonAlphaNumeric(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
            RemoveNonAlphaNumeric = xStrR
End Function

Apply a User Defined Function to Remove Non-Alphanumeric Characters in Excel

Step 2:

  • Our code is written. Now go back to the worksheet and type the function =RemoveNonAlphaNumeric. We can see that the function is created.

Apply a User Defined Function

  • Now apply the function in cell C4. The function is,
=RemoveNonAlphaNumeric(B4)

Apply a User Defined Function

  • Press ENTER to get the result.

Apply a User Defined Function

  • Our custom-made function is working properly. We will now apply this function to the rest of the cells to get the final result.

Apply a User Defined Function

Read More: How to Remove Characters in Excel (6 Methods)


Things to Remember

👉 Creating a custom formula has more limitations than regular VBA macros. It cannot alter the structure or format of a worksheet or cell.

👉 If you do not have your developer tab visible, you can activate it using this instruction.

Customized Quick Access Toolbar → More Commands → Customize Ribbon → Developer  → OK


Conclusion

In this guide, we have gone through two different approaches. You are most welcome to comment if you have any questions or queries. Leave your feedback in the comment section. Thanks for visiting ExcelDemy. Keep Learning!


Related Articles

Asikul Himel
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo