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?
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
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.