How do I remove emojis from an excel sheet using V

Question

I have a spreadsheet that contains emojis, e.g.,

标签： excel vba

举报

Answer 1

Thank you to FunThomas for pointing out that emojis are represented as 2 characters in VBA. The revised VBA code that works based on this:

Sub Remove_Emojis()

    Cells.Replace What:=ChrW(-10197) & ChrW(-8701), Replacement:="", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False
End Sub

In my actual solution, I put this into a loop to remove all of the different emojis.

Answer 2

I did as small experiment, putting your smiley into excel and let the following code run:

Dim s
s = ActiveCell
Dim i As Long
For i = 1 To Len(s)
    Dim c
    c = Mid(s, i, 1)
    Debug.Print i, c, AscW(c)
Next i

My result was

 1            ?             -10179 
 2            ?             -8701 

So obviously, the single character is split into 2 inside VBA. AscW and it's pendant ChrW deal with 16bit, and the emoji is a 32bit char, so in VBA this emoji character is handled as if there are 2 characters in the string

I added the following code and voilà, the smiley char was gone:

Dim x
x = ChrW(-10179) & ChrW(-8701)

s = Replace(s, x, "(smiley)")
ActiveCell.Offset(0, 1) = s

Probably you have to experiment with the different emojis you are facing and build a list in your replacing routine.