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

[AD04]

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
	A	B
1	tweets	cleaned_tweets_with_emojis
2	Lets go @KingJames !!!! We got this @Lakers !!!!!!!! 11:11 #LakeShow
3	Cmon @Lakers we got this!! <U+0001F49C><U+0001F49B> #LakeShow #LAKERSNATION
4	Me giving my energy to the @Lakers so we can win in the 4th quarter #LakeShow #LakersNation https://t.co/GbMHFaFwEE
5	Okay okay...I guess @Lakers, we can’t make it too easy...Lakers in 5, we can give them this game <U+0001F605> #LakeShow
6	Helluva game from @JimmyButler. Let’s see if we get a repeat in game 3. The @Lakers can’t play this poorly again, right?! #LakeShow
7	Not 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
8	Great job @Lakers <U+0001F608><U+0001F4AA><U+0001F3FE><U+0001F4AA><U+0001F3FE> we still on top #NBAnaESPN #LakeShow
Sheet2

 

[GlennUK]

I can think of a way, assuming some things ... like, all emojis are together, and no other ">" character will be in the tweet, and if you are happy with 7 or so extra columns, and that the emojis will be in black-and-white, like this:

sandbox101.xlsb
	A	B	C	D	E	F	G	H	I
1	tweets	cleaned_tweets_with_emojis	emoj1	emoj2	emoj3	emoj4	emoj5	emoj6	all emoji
2	Lets go @KingJames !!!! We got this @Lakers !!!!!!!! 11:11 #LakeShow	Lets go @KingJames !!!! We got this @Lakers !!!!!!!! 11:11 #LakeShow
3	Cmon @Lakers we got this!! <U+0001F49C><U+0001F49B> #LakeShow #LAKERSNATION	Cmon @Lakers we got this!! ?? #LakeShow #LAKERSNATION	?	?					??
4	Me giving my energy to the @Lakers so we can win in the 4th quarter #LakeShow #LakersNation https://t.co/GbMHFaFwEE	Me giving my energy to the @Lakers so we can win in the 4th quarter #LakeShow #LakersNation https://t.co/GbMHFaFwEE
5	Okay okay...I guess @Lakers, we can’t make it too easy...Lakers in 5, we can give them this game <U+0001F605> #LakeShow	Okay okay...I guess @Lakers, we can't make it too easy...Lakers in 5, we can give them this game ? #LakeShow	?						?
6	Helluva game from @JimmyButler. Let’s see if we get a repeat in game 3. The @Lakers can’t play this poorly again, right?! #LakeShow	Helluva game from @JimmyButler. Let's see if we get a repeat in game 3. The @Lakers can't play this poorly again, right?! #LakeShow
7	Not 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	Not 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
8	Great job @Lakers <U+0001F608><U+0001F4AA><U+0001F3FE><U+0001F4AA><U+0001F3FE> we still on top #NBAnaESPN #LakeShow	Great job @Lakers ????? we still on top #NBAnaESPN #LakeShow	?	?	?	?	?		?????
Sheet10

Cell Formulas
Range	Formula
B2:B8	B2	=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),"’","'"))
C2:H8	C2	=IFERROR(UNICHAR(HEX2DEC(MID($A2,FIND("^",SUBSTITUTE($A2,"<U+","^",VALUE(RIGHT(C$1,1))))+6,5))),"")
I2:I8	I2	=C2&D2&E2&F2&G2&H2

 

[AD04]

Hi, Glenn. Your response is much appreciated. I'm unable to completely wrap my head around how you came up with the B2 formula. Since I've to extract emojis for thousands of tweets with multiple permutations of text, characters, and emojis, could you please explain the formula?
Thanks, again.

 

[GlennUK]

Hi AD04, the B2 formula simply replaces whatever is between "<U+" and the final ">" with whatever is in cell I2 (and convert "’" to apostrophe). Do you need more details?

 

[GlennUK]

Also, do you need alternative formulas ... for example, do you have emojis that are not all in one lump (clump, or contiguous set, or together, or however you want to say it)?

 

[GlennUK]

Just in case:

hexcode_to_emoji_by_formula.xlsx
	A	B	C	D	E	F	G	H	I
1	tweets	cleaned_tweets_with_emojis	change code for apostrophe to real one	replace 1	replace 2	replace 3	replace 4	replace 5	replace 6
2	Lets go @KingJames !!!! We got this @Lakers !!!!!!!! 11:11 #LakeShow	Lets go @KingJames !!!! We got this @Lakers !!!!!!!! 11:11 #LakeShow	Lets go @KingJames !!!! We got this @Lakers !!!!!!!! 11:11 #LakeShow
3	Cmon @Lakers we got this!! <U+0001F49C><U+0001F49B> #LakeShow #LAKERSNATION	Cmon @Lakers we got this!! ?? #LakeShow #LAKERSNATION	Cmon @Lakers we got this!! <U+0001F49C><U+0001F49B> #LakeShow #LAKERSNATION	Cmon @Lakers we got this!! ?<U+0001F49B> #LakeShow #LAKERSNATION	Cmon @Lakers we got this!! ?? #LakeShow #LAKERSNATION
4	Me giving my energy to the @Lakers so we can win in the 4th quarter #LakeShow #LakersNation https://t.co/GbMHFaFwEE	Me giving my energy to the @Lakers so we can win in the 4th quarter #LakeShow #LakersNation https://t.co/GbMHFaFwEE	Me giving my energy to the @Lakers so we can win in the 4th quarter #LakeShow #LakersNation https://t.co/GbMHFaFwEE
5	Okay okay...I guess @Lakers, we can’t make it too easy...Lakers in 5, we can give them this game <U+0001F605> #LakeShow	Okay okay...I guess @Lakers, we can't make it too easy...Lakers in 5, we can give them this game ? #LakeShow	Okay okay...I guess @Lakers, we can't make it too easy...Lakers in 5, we can give them this game <U+0001F605> #LakeShow	Okay okay...I guess @Lakers, we can't make it too easy...Lakers in 5, we can give them this game ? #LakeShow
6	Helluva game from @JimmyButler. Let’s see if we get a repeat in game 3. The @Lakers can’t play this poorly again, right?! #LakeShow	Helluva game from @JimmyButler. Let's see if we get a repeat in game 3. The @Lakers can't play this poorly again, right?! #LakeShow	Helluva game from @JimmyButler. Let's see if we get a repeat in game 3. The @Lakers can't play this poorly again, right?! #LakeShow
7	Not 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	Not 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	Not 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
8	Great job @Lakers <U+0001F608><U+0001F4AA><U+0001F3FE><U+0001F4AA><U+0001F3FE> we still on top #NBAnaESPN #LakeShow	Great job @Lakers ????? we still on top #NBAnaESPN #LakeShow	Great job @Lakers <U+0001F608><U+0001F4AA><U+0001F3FE><U+0001F4AA><U+0001F3FE> we still on top #NBAnaESPN #LakeShow	Great job @Lakers ?<U+0001F4AA><U+0001F3FE><U+0001F4AA><U+0001F3FE> we still on top #NBAnaESPN #LakeShow	Great job @Lakers ??<U+0001F3FE><U+0001F4AA><U+0001F3FE> we still on top #NBAnaESPN #LakeShow	Great job @Lakers ???<U+0001F4AA><U+0001F3FE> we still on top #NBAnaESPN #LakeShow	Great job @Lakers ????<U+0001F3FE> we still on top #NBAnaESPN #LakeShow	Great job @Lakers ????? we still on top #NBAnaESPN #LakeShow
example mixed emojis and text

Cell Formulas
Range	Formula
B2:B8	B2	=INDEX(C2:I2,7-COUNTIF(C2:I2,""))
C2:C8	C2	=SUBSTITUTE(A2,"’","'")
D2:I8	D2	=IF(ISERROR(FIND("<U+",C2)),"",REPLACE(C2,FIND("<U+",C2),12,UNICHAR(HEX2DEC(MID(C2,FIND("<U+",C2)+6,5)))))

 

[Eric W]

You could use a macro as well:

Sub testA()
Dim FindCell As Range, mr As Range, a As Long, b As String, c As Long

    Set mr = Range("A2:A10")
    mr.Replace what:="’", replacement:="'"
    Do
        Set FindCell = mr.Find("<U+")
        If FindCell Is Nothing Then Exit Do
        a = InStr(FindCell.Value, "<U+")
        b = Mid(FindCell.Value, a, 12)
        c = WorksheetFunction.Hex2Dec(Mid(b, 4, 8))
        mr.Replace what:=b, replacement:=WorksheetFunction.Unichar(c)
    Loop
        
End Sub

Set the range on the first line. Then run it, and it will replace the <U+ ... with the actual emoji.

 

[AD04]

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?

 

[AD04]

Also, do you need alternative formulas ... for example, do you have emojis that are not all in one lump (clump, or contiguous set, or together, or however you want to say it)?

I'd really appreciate that, @GlennUK . And yes, the emojis are all over the place, usually in the middle and the end of the tweet but not necessarily altogether.

 

[AD04]

Hi, @Eric W . Thanks for the macro. I noticed the tweets have funky characters as follows:
- ÂÂ
- â€Å

I'd like to remove these with a blank space.