CODEWITHSUNDEEP
regexgoogle-sheetssplituniquegoogle-sheets-formula
DrPaul
DrPaul

Reputation: 45

Extract a list of unique text characters/ emojis from a cell

I have a text in cell (A1) like this: ✌😋👅👅☝️😉🍌🍪💧💧

I want to extract the unique emojis from this cell into separate cells: ✌😋👅☝️😉🍌🍪💧

Is this possible?

Upvotes: 2

Views: 525

Answers (3)

Max Makhrov
Max Makhrov

Reputation: 18717

Formula

Appears, one of the best formula solutions would be:

=SPLIT(REGEXREPLACE(A1,"(.)","$1@"),"@")

You may also add some additional checks like skin tones & intermediate chars:

=TRANSPOSE(SPLIT(REGEXREPLACE(A2,"(.[🏻🏼🏽🏾🏿"&CHAR(8205)&CHAR(65039)&"]*)","@$1"),"@"))

It will help to join some emojis as a single emoji.

Script

More precise way is to use the script:

https://github.com/orling/grapheme-splitter/blob/master/index.js

↑

  1. Add the code to Script editor
  2. Add code for sample usage:
function splitEmojis(string) {
  var splitter = new GraphemeSplitter();
  // split the string to an array of grapheme clusters (one string each)
  var graphemes = splitter.splitGraphemes(string);
  return graphemes;
}

Tests

Not 100% precise

1

Please note: some emojis are not correctly shown in sheets

enter image description here

🏴󠁧󠁢󠁷󠁬󠁳󠁿🏴󠁧󠁢󠁳󠁣󠁴󠁿🏴󠁧󠁢󠁥󠁮󠁧󠁿🏴

↑ emojis:

  • flag: England
  • flag: Scotland
  • flag: Wales
  • black flag

are the same for Google Sheets.

2

Vlookup function in #GoogleSheets and in #Excel thinks chars

  • #️⃣ and
  • *️⃣

are the same!

Upvotes: 0

Tanaike
Tanaike

Reputation: 201533

  • You want to put each character of ✌😋👅👅☝️😉🍌🍪💧💧 to each cell by splitting using the built-in function of Google Spreadsheet.

Sample formula:

=SPLIT(REGEXREPLACE(A1,"(.)","$1@"),"@")
  • ✌😋👅👅☝️😉🍌🍪💧💧 is put in a cell "A1".
  • Using REGEXREPLACE, @ is put to between each character like ✌@😋@👅@👅@☝@️@😉@🍌@🍪@💧@💧@.
  • Using SPLIT, the value is splitted with @.

Result:

enter image description here

Note:

  • In your question, the value of ️ which cannot be displayed is included. It's \ufe0f. So "G1" can be seen like no value. But the value is existing. So please be careful this. If you want to remove the value, you can use ✌😋👅👅☝😉🍌🍪💧💧.

References:

Added:

From marikamitsos's comment, I could notice that my understanding was not correct. So the final result is as follows. This is from marikamitsos.

=TRANSPOSE(UNIQUE(TRANSPOSE(SPLIT(REGEXREPLACE(A1,"(.)","$1@"),"@"))))

Upvotes: 4

player0
player0

Reputation: 1

or try:

=TRANSPOSE(UNIQUE(TRANSPOSE(REGEXEXTRACT(A1, REPT("(.)", LEN(A1))))))

0

Upvotes: 0

Related Questions