I do Google sheets and I'm fighting with REGEXREPLACE function past few hours, in general I have this:
string1: "blah blah blah @abc blah @bcd blah blah"
And I want this:
string2: "@abc @bcd"
I try to extract it with REGEXEXTRACT(string1,"@[^\s]+") but this function won't work global, so I use REGEXREPLACE to replace everything else than @abc and @bcd to "", but to do that I need to negate @[^\s]+!
How to do that?
I think this can helps (With $1 substitution):
/(?:.*?(@\S*))|(.*)/ig
[Regex Demo]
(edit) I suggested the other answers wouldn't work in Sheets, but I didn't look at @shA.t's answer closely, which in fact works perfectly. This would be the Sheets implementation:
=REGEXREPLACE(L1,"(?:.*?(@\S*))|(.*)","$1")
My original answer:
Unfortunately I think the other answers won't work in the context of Google Sheets, which doesn't support "look-aheads" in spreadsheet function regex.
See if this works:
=ArrayFormula(TRIM(JOIN(" ",IFERROR(REGEXEXTRACT(SPLIT(L1," "),"^@.+")))))
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With