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?
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 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.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 29 days agoHi. I used this just now to remove Chinese characters from may crew list but it also removed the spaces in between the alpha names. Hoping that there is an option to remove non-alpha characters but leave out spaces in the process.