Emoji Unicode VBA replace code with icon in cells

Hi,

I have a question about Emoji and making sense of a lot of unicode riddled in my sheet.

I have a list of all the possible unicodes from Github - It won't let me post the link now but if you search google for "gemoji/support.md" it is the top of the list.

There are 1432 examples in the list and I think around 150 have been used so it is too many for the SUBSTITUTE function. I've copied the list into excel and they work as icon emoji in the sheet.

And so its possible to use VBA to cycle through around 5,000 lines in one column, search for the unicode reference and then replace with the actual emojis?

Thanks for your help, I know this may be very challenging but if anyone has time to help that would be much appreciated.

A!

Hi, welcome to the forum

Sorry, so what exactly is it yo are trying to do here?
(there really is only 1184 emoji, the bottom section is just letters - not that it really matters)

Hi Ford,

Thank you for coming back on this, I appreciate your help. So here is an example of one line, lets say A1 that is held in the sheet named text.

Please Login or Register  to view this content.

And so breaking it down

\u2764\uFE0F is heart ❤️
\uD83C\uDFCA is swimming_man; swimmer ��
\uD83D\uDE0E is sunglasses ��

So I need to try to have Excel transform the line into this instead

Please Login or Register  to view this content.

Programatically one thing I've noticed is that when searching for heart ❤️ (\u2764\uFE0F) it's unicode also forms part of the longer couple_with_heart_woman_woman (\ud83d\udc69\u200d\u2764\ufe0f\u200d\ud83d\udc69) and so I guess if using a list search that may be something to take into consideration.

I have about 5,000 messages, around 50% of them have between 1 and 4 emojis in there and many don't make sense without them. They are all separated out with a space at either side and so the full string match should be possible but I aren't sure how to go about creating a list useable as a lookup in VBA or how to cycle through the cells and check for matches.

Hope this makes sense, please let me know if I can clarify or send more examples. If we can get it working I'll post a working version for other people to use and we can adjust the wording and hashtags based on your advice so it can help people in future. I guess quite a lot of people want to do this, and growing but there hasn't been any examples of it I can find anywhere.

Regards, A!

Maybe :

Please Login or Register  to view this content.

Sorry I double posted the same message somehow here

Yes, VBScript.RegExp is only available on Windows OS, so please try this code instead :

Please Login or Register  to view this content.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

You are welcome, glad to help.

Regards