Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

regex negative query on string RE2 google sheets

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?

like image 614
mmd Avatar asked Dec 02 '25 00:12

mmd


2 Answers

I think this can helps (With $1 substitution):

/(?:.*?(@\S*))|(.*)/ig

[Regex Demo]

like image 93
shA.t Avatar answered Dec 03 '25 16:12

shA.t


(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," "),"^@.+")))))

like image 44
AdamL Avatar answered Dec 03 '25 17:12

AdamL



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!