Finding and Deleting All Emojis in a Google Spreadsheet

Question

I have a Google Spreadsheet with thousands of cells with each cell being populated with strings with many different emojis.

Example of entries:

"Lol 😊","Haha 😊","Fire 🔥","👏👏👏Awesome!","Nice👍 See you tomorrow!😀", "こんにちは😊", "你好😀"

But I want to delete all of the emojis, is there a search function I can run/piece of Spreadsheet code I can run to make the document devoid of emojis?

Answer 1

Cleaning Up with Regular Expressions

I don't have the time to do the whole thing but this will give you a start. I cleaned everything in one cell with this.

var sht = SpreadsheetApp.getActiveSheet();
  var text = sht.getActiveCell().getValue();
  var cleantext = text.replace(/[^\s\w]/g,'');//replace everything that's not whitespace or word characters with null
  sht.getActiveCell().setValue(cleantext);

I used the line you provided as test data. Admittedly it needs a little tweaking because it's getting rid of some punctuation.

This is a little better.

function test()
{
  var sht = SpreadsheetApp.getActiveSheet();
  var text = sht.getActiveCell().getValue();
  var cleantext = text.replace(/[^\s\w"!,]/g,'');//added "!,
  sht.getActiveCell().setValue(cleantext);

}

So as you run it you may want to add a few more characters to don't replace list. That's it.

I have an expense report that I use to collect my expenses in different categories and I like to produce pie charts to help me get a big picture view of where my money is going. I use this Array Formula to help me gather the information into useful categories for me.

=ArrayFormula(IF(Row(C:C)=1,"Title",IF(LEN(C:C),IF(REGEXMATCH(C:C,"(?i)(string1|string2|string3|string4)"),D:D,""),)))

The regular expression provides an or function for adding additional matching for unexpected item appearing on my expense lists that I want to gather into these categories. If you need another matching term you just go into that formula and add another term as shown below

(string1|string2|string3|string4||string5)

The strings are replaced with real terms with no quotes unless they have quotes around them in the search target.