3

Example:

A2 -> What a beautiful day! #sunday #summer
B3 -> #news Stay tuned for our next contest! #something

Desired output on B2 ->#sunday #summer
Desired output on B3 ->#news #something

The closest I've found is REGEXEXTRACT but it only delivers the first word that begins with a #:

=REGEXEXTRACT(A2;"#[A-z0-9]+")
  • 1
    Aside: A-z is a bad practice, because ASCII table has a bunch of characters in this range that you don't want. A-Za-z would be better. But \w is what Twitter really uses: it allows A-Z, a-z, 0-9 and the underscore. – user79865 Feb 25 '16 at 19:13
6

Although regexextract can return an array, you'd need to know how many there are, so this isn't useful in your case. The way to get around this limitation of regexextract is to use regexreplace to get rid of the text you don't want. Like this:

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

The regular expression matches the words not beginning with # and replaces them with empty string. The trim function cleans up possible extra spaces that remain.

Better trimming

The above works fine for your examples, but has some defects in more complex situations:

Breaking #news-in just now! A new photo #contest! Code name #as_shot (important underscore here). #something. 

The above command returns

#news-in #contest! #as_shot #something. 

not realizing that hashtags cannot include -!. (they are limited to "word characters", A-Za-z0-9_). So, another regexreplace is needed to clean up. I put both in one command: such nesting of regexreplace is pretty common.

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

The outer replacement removes the parts of hashtags from the first character that's neither \w nor # to the end. The output has correct hashtags:

#news #contest #as_shot #something

Simplification

It seems that both regexreplace commands can be rolled into one, by giving it both of the above regexes as alternatives. The logic of this combined regex is harder to follow, but it has worked in all examples I tried.

=trim(regexreplace(A2, "((^|\s)[^#]\S*)|([^#\w\s]\S*)", ""))
  • This answer deserves 10 upvotes :) – daniel Feb 26 '16 at 1:24
1

The other alternative is to combine split, join, repeat with regex extract like this:

=join(" ",REGEXEXTRACT(A1,rept(".*(#\w+\S?\w+)",counta(split(A1,"#"))-1)))

it basically split out the text by # and then repeats the generic regex to capture it that number of times (-1 for the additional cell it puts out when you use split) - Also it is able to include the occasionally extra character you may come across such as a - or _ with the \S?

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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