How to remove all emojis?

[attv]

Hi! I'm cleaning text. How to remove all emojis from cells? Some examples of what I have below

🛩 H225 caracal , cougar ,AS332 super puma, écureuils, dauphin,h160,tigre,NH90 C-130 hercules, Airbus 319,320 Boeing 707,777​

Senior Vice President|Founder & CEO, GirlzWhoSell|2X 2021 Gold Stevie Award Winner|Top 50 Women|Author|DE&I Advocate|VC Investor|Keynote Speaker|Board Member|Podcast Host|Women in Sales Champion|#SellLikeAGirl​

MulticulturalMultilingual 🗣 Mentor​

B2B Sales | Territory Account Executive | EdTech​

Connecting people & ideas to create community, impact, & traction for early-stage founders | Chief of Staff ° Startup Mentor ° Intrapreneur ° Community Builder ° Strategy ° Operations ° LinkedIn Top 100 Sales ️​

Hospitality Management / Law Student / BA-Sociology / BA-International Relations / Former NGO Professional / Rights Activist​

Senior Flutter developer at Pegotec.​

𝐂𝐨-𝐅𝐨𝐮𝐧𝐝𝐞𝐫 𝐚𝐭 𝐓𝐞𝐜𝐡𝐓𝐨𝐬𝐞 | 𝐔𝐗 𝐃𝐞𝐬𝐢𝐠𝐧𝐞𝐫 | 𝐔𝐈 𝐃𝐞𝐯𝐞𝐥𝐨𝐩𝐞𝐫 |𝐔𝐬𝐞𝐫 𝐄𝐱𝐩𝐞𝐫𝐢𝐞𝐧𝐜𝐞 𝐒𝐩𝐞𝐜𝐢𝐚𝐥𝐢𝐬𝐭​

Building Community Around Founder Brands @ 1Strive | Your Community is Waiting | Consultant and Speaker Inspiring others to Drive Social Impact​

 

[jolivanes]

It will delete all shapes so if you have other shapes, like a button, let us know.

Sub Clear_Sheet1()
Dim shp As Shape
    For Each shp In Sheets("Sheet1").Shapes
        shp.Delete
    Next shp
End Sub

 

[attv]

What am I supposed to do with this? I've tried to save as a macro and run but it doesn't work.

 

[jolivanes]

Did you copy the code into a regular module?
What is the sheet name that has the shapes (emojis)?

 

[jolivanes]

Re: "What am I supposed to do with this?"

Insert and run VBA macros in Excel - step-by-step guide

This is a short step-by-step tutorial for beginners showing how to add VBA code (Visual Basic for Applications code) to your Excel workbook and run this macro to solve your spreadsheet tasks.

www.ablebits.com

 

[attv]

Actually, it does not work when pasted. Seems that those emojis are as text not as a shape. I've tried from the internet some other formula below, and it sometimes works, when there is like 1 emoji in the cell. Usually, when there are more it doesn't work though.


Sub kleanIt()
Dim r As Range, v As Variant, L As Long
Dim CH As String, i As Long, N As Long

Set r = ActiveCell
v = r.Value
L = Len(v)

For i = L To 1 Step -1
CH = Mid(v, i, 1)
N = AscW(CH)
If N < 1 Or N > 256 Then
v = Replace(v, CH, "")
End If
Next i

r.Value = v
End Sub