I have the following text in Cell A1 in google sheets with multiple linebreaks:
A paragraph is a series of sentences
that are organized and coherent, Zing
and are all related to a single topic.
Almost every piece of writing you do
that is longer than a few sentences Zing
should be organized into paragraphs.Zing
... One of the most important of these
is a topic sentence. Zing
And I would need to extract all the lines that ends with the word Zing so the output becomes(following is the expected output):
that are organized and coherent, Zing
that is longer than a few sentences Zing
should be organized into paragraphs.Zing
is a topic sentence. Zing
First i tried using regexextract formula "=REGEXEXTRACT(A1,".*Zing")", but unfortunately it returns only the 1st occurence that are organized and coherent, Zing
Then I tried using regexreplace to replace all other lines that doesnt contain or ends with Zing, but it did not work.
=REGEXREPLACE(A1, ".*Zing", ",")
This returned:
"A paragraph is a series of sentences
,
and are all related to a single topic.
Almost every piece of writing you do
,
,
... One of the most important of these
,"
but I want the missing lines, how to exclude the above and to extract only the missed lines
I would need a formula in excel or googlesheets to match the expected output
CodePudding user response：
In your situation, how about the following sample formula?
Sample formula:
=JOIN(CHAR(10),REGEXEXTRACT(A1,REGEXREPLACE(A1,"(.*Zing)","($1)")))
When you want to put each line to each row, you can also use the following formula.
=TRANSPOSE(REGEXEXTRACT(A1,REGEXREPLACE(A1,"(.*Zing)","($1)")))
Result:
