Delete non-English/alphanumeric characters in Excel workbook

5 pts.
Tags:
Microsoft Excel
Visual Basic
How can I delete non-English/characters, special characters, unnecessary spaces? I have a Microsoft Excel Workbook where I've 240 Thousand different type of  descriptions that are alphanumeric and Chinese characters. Examples:
???? ??? ?? ?????<?? ??(??

? ?? ? ?/ ???????? ????
I need to be able to clean these up by VB formula:
  • Delete non-English characters
  • Delete special characters
  • Delete spaces between numbers
Can any one help me? Thanks!
1

Answer Wiki

Thanks. We'll let you know when a new response is added.
Try this language conversion
Public Sub TranslateSheet()
    Dim wksForeign As Excel.Worksheet
    Dim wksResults As Excel.Worksheet
    Dim rngCell As Excel.Range

    Set wksForeign = Sheet1
    Set wksResults = ThisWorkbook.Worksheets.Add(After:=wksForeign)
    wksResults.Name = "Translation of " & wksForeign.Name

    Call wksForeign.Cells.Copy(Destination:=wksResults.Range("A1"))
    
    For Each rngCell In wksResults.UsedRange.SpecialCells(xlCellTypeConstants, 2)
        rngCell.Value2 = Translate(rngCell.Value2, Spanish, English)
    Next rngCell
End Sub

Private Const strSHORTCODES As String = ",en,af,sq,ar,hy,az,eu,be,bn,bg,ca,zh,hr,cs,da,nl,eo,et,tl,fi,fr,gl,ka,de,el,gu,ht,iw,hi,hu,is,id,ga,it,ja,kn,ko,lo,la,lv,lt,mk,ms,mt,no,fa,pl,pt-PT,ro,ru,sr,sk,sl,es,sw,sv,ta,te,th,tr,uk,ur,vi,cy,yi"

Public Enum eLanguage
    Auto_Detect
    English
    Afrikaans
    Albanian
    Arabic
    Armenian
    Azerbaijani
    Basque
    Belarusian
    Bengali
    Bulgarian
    Catalan
    Chinese
    Croatian
    Czech
    Danish
    Dutch
    Esperanto
    Estonian
    Filipino
    Finnish
    French
    Galician
    Georgian
    German
    Greek
    Gujarati
    Haitian_Creole
    Hebrew
    Hindi
    Hungarian
    Icelandic
    Indonesian
    Irish
    Italian
    ***anese
    Kannada
    Korean
    Lao
    Latin
    Latvian
    Lithuanian
    Macedonian
    Malay
    Maltese
    Norwegian
    Persian
    Polish
    Portuguese
    Romanian
    Russian
    Serbian
    Slovak
    Slovenian
    Spanish
    Swahili
    Swedish
    Tamil
    Telugu
    Thai
    Turkish
    Ukrainian
    Urdu
    Vietnamese
    Welsh
    Yiddish
End Enum

Public Function Translate(ByVal strText As String, _
                          Optional ByVal eFrom As eLanguage = Auto_Detect, _
                          Optional ByVal eTo As eLanguage = English) As String
    Dim strUrl As String
    Dim strResult As String

    strText = Replace$(strText, Chr$(32), "%20")
    strText = Replace$(strText, Chr$(160), "%20")

    strUrl = "http://translate.google.com/translate_a/t?client=t&text={S}&hl=en&sl={F}&tl={T}&multires=1&pc=0&rom=1&sc=1"
    strUrl = Replace$(strUrl, "{S}", strText)
    strUrl = Replace$(strUrl, "{F}", Split(strSHORTCODES, ",")(eFrom))
    strUrl = Replace$(strUrl, "{T}", Split(strSHORTCODES, ",")(eTo))

    With CreateObject("MSXML2.XMLHTTP")
        Call .Open("get", strUrl, False)
        Call .Send
        strResult = .responseText
    End With

    strResult = Replace$(Mid$(CStr(Split(strResult, ",")(0)), 4), Chr$(34), "")
    Translate = strResult
End Function

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: