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?
Enable you to do smarter, faster and better
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.
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 120 handy functions, makes your jobs 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.