0

I´m with a similar problem, like this post: How can social hashtags be extracted from a cell?

Basicly it asks what to do to extract hashtags from a cell in google sheets. Some good answers, like this almost perfect for me:

=regexreplace(trim(regexreplace(A2, "(^|\s)[^#]\S*","")), "[^#\w\s]\S*", "")

It works, but Portuguese has a lost of diacritics that was cut off with this approuch.

Botton line is: what to do to a hashtag like "#IssoÉumTesteComAcentuação" be extracted?

Thanks and forgive my terrible english. And this is my first time using this site! I like it!!

1

2 Answers 2

1

Use regexreplace() to remove all characters except those that match #ànyTéxt or #ànyTéxt!, i.e., a hashtag followed by whitespace or punctuation. For that to work, you'll have to append a space to the text you are searching, like this:

=trim(regexreplace(A2 & " ", "(#[^\s,.;?!]+)|(?:.|\n)", "$1 "))

The formula returns all hashtags in the same cell, separated with spaces. To get each hashtag in a cell of its own, use split(), like this:

=split(regexreplace(A3 & " ", "(#[^\s,.;?!]+)|(?:.|\n)", "$1 "), " ")

See RE2.

0

TL;DR

=LET(t, SPLIT(A1," "), FILTER(t, LEFT(t,1)="#"))

Below are a couple of approaches that don't use regular expressions and return the results in an array (one result per cell). One approach uses the FILTER function and the other uses QUERY to achieve the same thing.

FILTER

FILTERLEFTLETSPLITTOCOL

=LET(t, SPLIT(A1," "), FILTER(t, LEFT(t,1)="#"))
=FILTER(SPLIT(A1," "), LEFT(SPLIT(A1," "),1)="#")
  • Filters the array of text strings returned by SPLIT to include only those that start with "#"
  • The two versions above are equivalent. LET version stores the initial array of text in the (arbitrarily) named variable t for reuse which may be useful if the formula gets more complicated.
  • TOCOL can be added to return the results as a single column instead of row:
=LET(t, TOCOL(SPLIT(A1," ")), FILTER(t, LEFT(t,1)="#"))

  FILTER - Single Row Result
Example showing source data in A1, formula using the FILTER function in A2, and the expected results in A2:C2

  FILTER - Single Column Result
Example showing source data in A1, formula using the FILTER function in A2, and the expected results in A2:A4

QUERY

QUERYSPLITTOCOLTOROW

=QUERY(TOCOL(SPLIT(A1," ")), "where Col1 like '#%'")
  • Uses QUERY instead of FILTER to return the hashtags
  • TOROW can be added to return the results as a single row instead of column:
=TOROW(QUERY(TOCOL(SPLIT(A1," ")), "where Col1 like '#%'"))

  QUERY- Single Column Result
Example showing source data in A1, formula using the QUERY function in A2, and the expected results in A2:A4

  QUERY- Single Row Result
Example showing source data in A1, formula using the QUERY function in A2, and the expected results in A2:C2

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.