2

I'm trying to use this formula to extract hashtags from a cell. I got it from this very old topic.

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

Example source:

"This is a line before a line break.
#missinghashtag #2ndhashtag #3rdhashtag"

Current outcome:

#2ndhashtag #3rdhashtag

Desired outcome:

#missinghashtag #2ndhashtag #3rdhashtag

It mostly works, but I have found a small issue that I'm unable to debug as I'm clueless about REGEX.

It's skipping the first hashtag of a new paragraph. I guess it tries to find words that start with #, but a "break" counts as an invisible character before the #, so what happens is that the first hashtag doesn't get extracted. If I add a space after the break and before the first #, it suddenly works. And also I there's no space before the break, it also works. But would like to fix it to be bulletproof.

I only need to fix that. It would be highly appreciated.

Edit: Actually, I tested some more and if I add a hashtag before the break then something strange happens. The earlier conflictive hashtag works, and the one before the break works too, but they are returned with the break in between, which ideally I'd also like to remove.

Example source:

"This is a line before a line break. #thishashtagworks
#missinghashtag #2ndhashtag #3rdhashtag"

Current outcome:

"#thishashtagworks
#missinghashtag #2ndhashtag #3rdhashtag"

Desired outcome:

#thishashtagworks #missinghashtag #2ndhashtag #3rdhashtag
Improve this question
2
  • The formula doesn't return to me the reported result, it returns #missinghashtag #2ndhashtag #3rdhashtag i.stack.imgur.com/v5fLo.png I copy / pasted the sample text and the formula, just changing A2 to A1 to save space. Maybe the original source is some sort of hidden characters (html entities, unicode, ...?). Please add more details about what is the source of the sample text, i.e. if you typed directly into the edit box or if you copied and pasted, if you copied and pasted, please use the web browser inspector tool to check the source code of the original text.
    – Rubén ♦
    Feb 9 at 17:59
  • I also guess it's some sort of invisible character, I thought it was a break, but doesn't seem to be. The text came from Airtable, and some other user typed it, so can't be sure. prnt.sc/cxUx3tUzAwrQ In the HTML no character appears, not even the break tbh. Bout you can see the break in the field, and when I copy pasted it in the example spreadsheet I shared. docs.google.com/spreadsheets/d/…
    – Chaoscontrol
    Feb 10 at 9:50

1 Answer 1

Reset to default
2

Try this one:

=trim(regexreplace(A2, "(\r+)|(\n+)|((^|\s)[^#]\S*)|([^#\w\s]\S*)", " "))
Improve this answer
9
  • Thanks for the help! Your solution works to solve the 2nd issue, and removes the line break delivering the result in a single line. But when removing the hashtag before the line break, the hashtag right after still doesn't get extracted. :(
    – Chaoscontrol
    Feb 9 at 11:35
  • @Chaoscontrol - Please remember that you can also contribute to this community that helps you. As per site guidelines when an answer addresses your question, accept it and even upvote it so others can benefit as well.
    – Rohit Gupta
    Feb 9 at 11:49
  • @RohitGupta I did (upvote it). :) I did not accept is as a complete solution as it didn't solve my main problem.
    – Chaoscontrol
    Feb 9 at 15:34
  • @Chaoscontrol please post a link to a file that shows your problem
    – Daniele
    Feb 10 at 8:54
  • 1
    ok, now go and see your modified file :) the final formula being: =trim(regexreplace(A1, "(\s\r+)|(\s\n+)|(\n+)|((^|\s)[^#]\S*)|([^#\w\s]\S*)", " "))
    – Daniele
    Feb 11 at 3:26

Your Answer

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

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