I have a string opposed to unit sales @d. Moving rev @i. Before used to down @test.
I need to extract all of the text with @ tags before the period including the @.
The results should be: @d @i @test
I can match everything using @([^\.]*) but I read that since google sheet doesn't do multiple extract, i need to replace everything except my pattern with " " then split to get the results.
I'm having problems producing a formula that uses regexreplace for everything except my pattern.
The commonly accepted way to do this is by using REGEXREPLACE to capture all the matches
=REGEXREPLACE(A1,"@\w+","($0)")
And then using REGEXEXTRACT to extract them
=REGEXEXTRACT(A1,REGEXREPLACE(A1,"@\w+","($0)"))
If the string contains regex special characters, we have to escape them first
=REGEXEXTRACT(A1,REGEXREPLACE(REGEXREPLACE(A1,"[()\[\]{}|\\^$.+*?]","\\$0"),"@\w+","($0)"))
regex way:
=regexreplace(regexreplace(A2,"(@[^@\.]+)\.( )?|.","$1$2")," $",)
non-regex solution(joined in single cell):
=let(Σ,split(A2," "),textjoin(" ",1,index(if((left(Σ)="@")*(right(Σ)="."),substitute(Σ,".",),))))
non-regex solution(split across cells):
=let(Σ,split(A2," "),torow(index(if((left(Σ)="@")*(right(Σ)="."),substitute(Σ,".",),)),1))
You can use the following simple formula
=TRIM(REGEXREPLACE(C1,"(@\w+)|.", "$1 "))
(Do adjust the formula according to your ranges and locale)
Functions used:
