Removing all chinese text

[leonardtan]

Dear Experts,

I searched online for all available forum to remove chinese text but was unable to solve the problem.

I have a survey form, i guess in order to use vba to automate pivot will need to remove Chinese text.

I have written the code to run this, but got stuck when running pivot.

attached file sample

 

[Chihiro]

You could do something like below with your data set.

Sub remove_Chinese()
Dim ar, x
Dim ws As Worksheet
Dim i As Long, j As Long, iCh As Long
ar = Sheet1.Range("A1").CurrentRegion.Value2

For i = 1 To UBound(ar)
    For j = 1 To UBound(ar, 2)
        For iCh = 1 To Len(ar(i, j))
            If Asc(Mid(ar(i, j), iCh, 1)) = 63 Then
                ar(i, j) = Replace(ar(i, j), Mid(ar(i, j), iCh, 1), " ")
            End If
        Next
        ar(i, j) = Trim(ar(i, j))
    Next
Next
Set ws = Worksheets.Add(After:=Worksheets(Worksheets.Count))
ws.Range("A1").Resize(UBound(ar), UBound(ar, 2)) = ar
End Sub

Add it to standard module in your data workbook and run it.

This code uses the fact... VBA isn't able to decode Unicode and, when ASC() function is used on such character, 63 is returned (i.e. code for ?). Unfortunate side effect is that it will also replace literal character for ? as well.

Edit: For clarity.

 

[Marc L]

Hi !

The AscW easy Way Without « unfortunate side effect »
to paste to raw data workbook :​

Sub Demo1()
         Dim Rg As Range, B%, N%, S$, C$
         Application.ScreenUpdating = False
    For Each Rg In Sheet1.UsedRange
             B = 0
             N = 1
             S = Rg.Value2
        Do Until N > Len(S)
                C = Mid$(S, N, 1)
            Select Case AscW(C)
                   Case Is < 0, Is > 10174
                        S = Replace$(S, C, "")
                        B = 1
                   Case Else
                        N = N + 1
            End Select
        Loop
             If B Then Rg.Value = S
    Next
         Application.ScreenUpdating = True
End Sub

Do you like it ? So thanks to click on bottom right Like !
​