How to remove non-English characters in Excel column?
For instance, ，here is a list of names which include some non-English characters. How can you quickly remove the cells which contain non-English characters in Excel?
Remove rows containing non-English characters by VBA
Remove non-English characters from string by Remove Characters
Remove rows contain non-English characters by VBA
Here is a VBA that can remove the rows which contain non-English characters in Excel.
1. Active the worksheet containing the characters you want to remove, and press Alt + F11 keys to enable the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste below code to the Module.
VBA: Remove non-English characters
Sub RemoveNonEnglish()
'UpdatebyExtendoffice20171204
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:", "KuTools For Excel", Selection.Address, , , , , 8)
If xRg Is Nothing Then Exit Sub
Application.ScreenUpdating = False
xRows = xRg.Rows.Count
Set xRg = xRg(1)
For I = 1 To xRows
Set xCell = xRg.Offset(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
I = I - 1
Exit For
End If
Next
End If
Next
Application.ScreenUpdating = True
MsgBox "Completed...", vbInformation
End Sub
3. Press F5 key to select the column list you want to use to in the popping dialog.
4. Click OK > OK, and then the rows containing non-English characters have been removed.
 |
 |
 |
Note: this VBA only works for one column.
Remove non-English characters from string by Remove Characters
If you want to remove non-English characters from string as below screenshot shown, you can apply Kutools for Excel’s Remove Characters feature to quickly finish it.
 |
|
 |
| Kutools for Excel, with more than 300 handy functions, makes your jobs more easier. |
After installing Kutools for Excel, please do as below:（Free Download Kutools for Excel Now!)
1. Select the range you need and click Kutools > Text > Remove Characters. See screenshot:
2. Check Non-alpha only in the Remove Characters dialog, you can see the result in the Preview first.
3. Click Ok, the non-English characters have been removed from strings.
Demo
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
