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.
How to Remove Non-Alphanumeric Characters in Excel: 2 Methods
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.
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.
- A new window is opened. Now click Insert and select Module to open a new module.
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
- Now run the code by pressing F5. Select your range and click OK to conclude the operation.
- We have successfully removed those non-alphanumeric characters from our cells.
Read More: How to Remove Non-Printable Characters in ExcelÂ
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 learn more about custom-made functions, Click Here!
Step 1:
- First, go to the Microsoft Visual Basic for Applications Window by pressing Alt+F11.
- Write down the code for your UFD. We have named our function RemoveNonNlphaNumeric. 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
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.
- Now apply the function in cell C4. The function is,
=RemoveNonAlphaNumeric(B4)
- Press ENTER to get the result.
- Our custom-made function is working properly. We will now apply this function to the rest of the cells to get the final result.
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
Download Practice Workbook
Download this practice book to exercise the task while you are reading this article.
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.
