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.
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!