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-Alpha Characters From Cells in Excel?
This manual will show you step-by-step how to purge any non-alphabetic characters from particular cells in order to clean up your Excel data. When dealing with data that comprises undesired symbols, numbers, or special characters, this strategy can be tremendously helpful and leave you with clean, well-organized data.
An effective tool for data analysis, reporting, and record-keeping is Excel. But occasionally, information could come from different places or be produced in methods that produce mixed content, such non-alphabetic characters. It can take a while to manually remove these undesirable characters, especially when dealing with huge datasets. Our tutorial can save the day in that situation. It's imperative to have a fundamental understanding of Excel and its features before we begin. This tutorial is appropriate for all ability levels, whether you are an experienced Excel user or a novice wishing to increase your knowledge.
Remove Non-Alpha Characters From Cells
Here, we will first create a VBA module and then run it to complete the task. So let us see a simple process to learn how you can remove non-alpha characters from cells in Excel.
Step 1
Consider an Excel sheet where you have cells with non-alpha 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 RemoveNotAlphas()
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "Remove Non Alpha"
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.]" 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. You will see that non-alpha characters will be removed.
F5 > Select Cells > OK
Conclusion
In this tutorial, we have used a simple example to demonstrate how you can remove non-alpha characters from cells in Excel to highlight a particular set of data.
127 Views
