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-English Characters in Excel Column?
Powerful software like Microsoft Excel is frequently used for data analysis, manipulation, and organisation. Working with datasets including characters from different languages can occasionally be difficult, particularly when you need to filter or clean the data.
This step-by-step tutorial will show you how to locate and eliminate non-English characters from an Excel column. This course will provide you the knowledge you need to effectively clean your data and make sure it complies with your language standards, regardless of whether you're working with messy data, inputs in a foreign language, or special characters that you want to eliminate from your analysis.
Remove Non-English Characters
Here, we can complete the task using the VBA application. So let us see a simple process to know how you can remove non-English characters in an Excel column.
Step 1
Consider an Excel sheet where you have cells with non-English 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 RemoveNonEnglish()
Dim xRg As Range
Dim xCell As Range
Dim I As Long
Dim J As Long
Dim xRows As Long
Dim xAsc As Long
On Error Resume Next
Set xRg = Application.InputBox("Select single column:", "Remove Non-English", Selection.Address, , , , , 8)
On Error GoTo 0 ' Always reset error handling after using it
If xRg Is Nothing Then Exit Sub
Application.ScreenUpdating = False
xRows = xRg.Rows.Count
For I = xRows To 1 Step -1 ' Looping backward to avoid issues with deleting rows
Set xCell = xRg.Cells(I, 1)
If xCell.Value <> "" Then
For J = 1 To Len(xCell.Value)
xAsc = Asc(UCase(Mid(xCell.Value, J, 1)))
If xAsc < 65 Or xAsc > 90 Then
xCell.EntireRow.Delete
Exit For ' Exit the inner loop when a non-English character is found
End If
Next J
End If
Next I
Application.ScreenUpdating = True
MsgBox "Completed...", vbInformation
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-English characters will be removed.
F5 > Select Cells > OK
This is how you can remove non-English characters in an Excel column.
Conclusion
In this tutorial, we have used a simple example to demonstrate how you can remove non-English characters in an Excel column to highlight a particular set of data.
632 Views
