Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to Remove Non-Alphanumeric Characters in Excel?
Excel is an effective tool for manipulating and analysing data, however occasionally the data we work with contains undesired characters that might make our duties more difficult. Non-alphanumeric characters can clog up our data and obstruct calculations, sorting, and other operations. Examples include symbols, punctuation marks, and other special characters. This article is made to make the process simple and clear, regardless of whether you are working with untidy text, unstructured data, or simply wish to clean up your information.
Remove Non-Alphanumeric Characters
Here we will first create a VBA module and then select the range of cells to complete the task. So let us see a simple process to learn how you can remove non-alphanumeric characters in Excel.
Step 1
Consider an Excel sheet where you have cells with non-alphanumeric characters, similar to the below image.
First, right-click on the sheet name and select View code to open the VBA application.
Right-click > View Code.
Step 2
Then click on Insert and select Module, then copy the below code into the text box.
Insert > Module > Copy.
Code
Sub RemoveNotAlphasNotNum()
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "Remove Non Alphanumeric"
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
Step 3
Then click F5 to run the module. Then select the range of cells and click OK to complete the task. Then you will see that non-alphanumeric characters will be removed.
F5 > Select Cells > OK
This is how you can remove non-alphanumeric characters in Excel.
Conclusion
In this tutorial, we have used a simple example to demonstrate how you can remove non-alphanumeric characters in Excel to highlight a particular set of data.
217 Views
