google-sheetsemojiformulas

How do I count all cells in a column that have emoji?

I have a problem with emoji in my production database. Since it's in production, all I get out of it is an auto-geneated excel spreadsheet (.xls) every so often with tens of thousands of rows. I use Google Sheets to parse this so I can easily share the results.

What formula can I use to get a count of all cells in column n that contain emoji?

For instance:

Data
+----+-----------------+
| ID | Name            |
+----+-----------------+
| 1  | Chad            |
+----+-----------------+
| 2  | ✨Darla✨         |
+----+-----------------+
| 3  | John Smith      |
+----+-----------------+
| 4  | Austin ⚠️ Powers |
+----+-----------------+
| 5  | Missus 🎂        |
+----+-----------------+

Totals
+----------------------------------+---+
| People named Chad                | 1 |
+----------------------------------+---+
| People with emoji in their names | 3 |
+----------------------------------+---+
Solution

  • Edit by Ben C. R. Leggiero:

    =COUNTA(FILTER(A2:A6;REGEXMATCH(A2:A6;"[^\x{0}-\x{F7}]")))