How to get emojis displayed in excel instead of the hex codes?

AD04

New Member
Joined
Jan 26, 2021
Messages
16
Office Version
  1. 365
Platform
  1. Windows
Hello. I've scraped tweets that contain emojis in an Excel sheet, however, the emojis/emoticons are displayed as hex codes. Could you please help me with getting them displayed as emojis?
Thanks.

g3l.xlsx
AB
1tweetscleaned_tweets_with_emojis
2Lets go @KingJames !!!! We got this @Lakers !!!!!!!! 11:11 #LakeShow
3Cmon @Lakers we got this!! <U+0001F49C><U+0001F49B> #LakeShow #LAKERSNATION
4Me giving my energy to the @Lakers so we can win in the 4th quarter #LakeShow #LakersNation https://t.co/GbMHFaFwEE
5Okay okay...I guess @Lakers, we can’t make it too easy...Lakers in 5, we can give them this game <U+0001F605> #LakeShow
6Helluva game from @JimmyButler. Let’s see if we get a repeat in game 3. The @Lakers can’t play this poorly again, right?! #LakeShow
7Not surprised of this result. We sorta knew @lakers werent going to come in as expected. It always takes a lost to wake up. On to the next. #lakersin5 #LakeShow #LakersNation
8Great job @Lakers <U+0001F608><U+0001F4AA><U+0001F3FE><U+0001F4AA><U+0001F3FE> we still on top #NBAnaESPN #LakeShow
Sheet2
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
That helps, thanks. How about the last bit of the code: -FIND("<U+",A2)+1,I2.

I get the part that the hexcodes are subtracted but why is I2 mentioned?

The contents of cell I2 are the replacement text ... that is the last argument of the REPLACE function. By the way, the second set of formulas (the posting that copes with emojis all over the place) operates a different way, slightly, and may be easier to understand. They will also be easier to tweak for any new processing ... like extending the SUBSTITUTE for apostrophes to cope with your other oddities, like:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"’","'"),"ÂÂ"," "),"â€Å"," ")
(am guessing those extra characters based on your last posting)
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

AD04

New Member
Joined
Jan 26, 2021
Messages
16
Office Version
  1. 365
Platform
  1. Windows
Hi, @Eric W. I notice the macro code you gave only helps replace a single hex code (like <U+0001F611>) with the emoji but not a series of them (like <U+0001F608><U+0001F4AA><U+0001F3FE><U+0001F4AA><U+0001F3FE>). Could you please help with a new micro code that helps substitute emojis for all hex codes?

Thanks.

emoji_applied.g3l_v2.xlsm
CD
1emoji_applied_by_Eric's_macroemoji_applied_all
2@Lakers Fr? Now they got confidence we gone have to destroy 😑 @AntDavis23 how you only put up 9 shots???? Yall please reply
3Not surprised of this result. We sorta knew @lakers werent going to come in as expected. It always takes a lost to wake up. On to the next. #lakersin5 #LakeShow #LakersNation
4Great job @Lakers <U+0001F608><U+0001F4AA><U+0001F3FE><U+0001F4AA><U+0001F3FE> we still on top #NBAnaESPN #LakeShow
5@Lakers were lackluster, pathetic, bull **** today!!! Got up very early in the morning to witness this dismal performance from them. A short handed heat team gave the Lakers a knock out punch hard in their face. Will Lakers wake up and comeback is wat we have to see #Lakeshow
6no kap tho , this @Lakers &amp; @MiamiHEAT game was pretty solid ; i enjoyed the excitement as i watched this #NBAFinals #nba #lakeshow till the end of me . 104-115 ~ we gotta do better than that 🤦<U+0001F3FD><U+200D><U+2642><U+FE0F> . #zayata <U+0001F4AA><U+0001F3FD><U+2728><U+0001F4AF><U+0001F525><U+0001F495> https://t.co/bi8haNiuVo
7WTF we doing @Lakers 🤦<U+0001F3FF><U+200D><U+2642><U+FE0F>
Sheet1
 

AD04

New Member
Joined
Jan 26, 2021
Messages
16
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi, @GlennUK. I know we resolved this but there seems to be a new development. Earlier, I was able to view the emoji in place of the hex codes after applying the formula you came up with and later explained. However, as I try to apply this again, it doesn't seem to work. To make matters worse, it is deleting the hex codes.

Do you mind giving me a hand with this? For simplicity purposes, let's forget about cleaning the greek characters and only convert the hex codes to emojis.

g3l.xlsx
AB
1tweetscleaned_tweets_with_emojis
2Lets go @KingJames !!!! We got this @Lakers !!!!!!!! 11:11 #LakeShowLets go @KingJames !!!! We got this @Lakers !!!!!!!! 11:11 #LakeShow
3Cmon @Lakers we got this!! <U+0001F49C><U+0001F49B> #LakeShow #LAKERSNATIONCmon @Lakers we got this!! #LakeShow #LAKERSNATION
4Me giving my energy to the @Lakers so we can win in the 4th quarter #LakeShow #LakersNation https://t.co/GbMHFaFwEEMe giving my energy to the @Lakers so we can win in the 4th quarter #LakeShow #LakersNation https://t.co/GbMHFaFwEE
5Okay okay...I guess @Lakers, we can’t make it too easy...Lakers in 5, we can give them this game <U+0001F605> #LakeShowOkay okay...I guess @Lakers, we can't make it too easy...Lakers in 5, we can give them this game #LakeShow
6Helluva game from @JimmyButler. Let’s see if we get a repeat in game 3. The @Lakers can’t play this poorly again, right?! #LakeShowHelluva game from @JimmyButler. Let's see if we get a repeat in game 3. The @Lakers can't play this poorly again, right?! #LakeShow
7Not surprised of this result. We sorta knew @lakers werent going to come in as expected. It always takes a lost to wake up. On to the next. #lakersin5 #LakeShow #LakersNationNot surprised of this result. We sorta knew @lakers werent going to come in as expected. It always takes a lost to wake up. On to the next. #lakersin5 #LakeShow #LakersNation
8Great job @Lakers <U+0001F608><U+0001F4AA><U+0001F3FE><U+0001F4AA><U+0001F3FE> we still on top #NBAnaESPN #LakeShowGreat job @Lakers we still on top #NBAnaESPN #LakeShow
Sheet2
Cell Formulas
RangeFormula
B2:B8B2=IF(ISERROR(FIND("<U+",A2)),SUBSTITUTE(A2,"’","'"),SUBSTITUTE(REPLACE(A2,FIND("<U+",A2),FIND("^",SUBSTITUTE(A2,">","^",LEN(A2)-LEN(SUBSTITUTE(A2,">",""))))-FIND("<U+",A2)+1,I2),"’","'"))
 

AD04

New Member
Joined
Jan 26, 2021
Messages
16
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

My bad, @GlennUK . I was only interested in the cleaned tweet so ignore the other columns. Thanks, again.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,840
To answer an earlier question, the 12 in the macro was the length of the field to extract. The usual Unicode indicator is <U+00001234> which is 12 characters. However, I noticed that some of them are only 8 long, like this: <U+1234> which might be why it didn't replace all of the codes. This updated macro should handle either case, and I added some extra code to handle the funky characters you mentioned.

VBA Code:
Sub testA()
Dim FindCell As Range, mr As Range, a As Long, a2 As Long, b As String, c As Long, misc As Variant

'Set the range here
    Set mr = Range("A2:A10")
    
'Put any replacements you want in this array, what it is, followed by what you want
    misc = Array("’", "'", "ââ", " ", "¬Å", " ", "Ã", " ", "Â", " ")
    For a = 0 To UBound(misc) Step 2
        mr.Replace what:=misc(a), replacement:=misc(a + 1)
    Next a
    
'This part looks for <U+ ... > and replaces with the equivalent Unicode character
    Do
        Set FindCell = mr.Find("<U+")
        If FindCell Is Nothing Then Exit Do
        a = InStr(FindCell.Value, "<U+")
        a2 = InStr(a, FindCell.Value, ">")
        If a2 = 0 Then
            MsgBox ("Exiting due to unmatched <U+ in " & FindCell.Value)
            Exit Sub
        End If
        b = Mid(FindCell.Value, a, a2 - a + 1)
        c = WorksheetFunction.Hex2Dec(Mid(b, 4, a2 - a - 3))
        mr.Replace what:=b, replacement:=WorksheetFunction.Unichar(c)
    Loop
        
End Sub
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,972
Messages
5,767,402
Members
425,410
Latest member
SmittyT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top