Extract Multiple Sections of Text in a String, Between Two Repeating Words

[blafarm]

Hi All,

I have a bit of a challenge and was hoping for some guidance.

I have a very long string of text in a single cell and I need to extract multiple sections of text that appear between two words that repeat in the string.

For example, here is a very simplified version of the text string in Cell A1:

{"date": 5/7/19 headline:"GE Posts Profit" source:"CNBC"}{"date": 5/8/19 headline:"GE Dividend Shrink" source:"MSN"}{"date": 5/9/19 headline:"GE Bankrupt" source:"WSJ"}

I have had limited success with the following formula, however it only extracts the text between the first appearance of the words: "headline" and "source":

Formula: =MID(A1,SEARCH("headline",A1)+2,SEARCH("source:",A1)-SEARCH("headline",A1)-4)

Result: GE Posts Profit

This formula does not capture ALL of the headlines in the full string -- only the first headline.

My initial goal is to learn a formula that will extract ALL of the headlines within the string, and that will place the results in a succession of horizontal or vertical cells that either look like this:

[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD]GE Posts Profit[/TD]
[TD]GE Dividends Shrink[/TD]
[TD]GE Bankrupt[/TD]
[/TR]
</tbody>[/TABLE]

... or that look like this:

[TABLE="class: grid, width: 150"]
<tbody>[TR]
[TD]GE Posts Profit[/TD]
[/TR]
[TR]
[TD]GE Dividends Shrink[/TD]
[/TR]
[TR]
[TD]GE Bankrupt[/TD]
[/TR]
</tbody>[/TABLE]

I have some additional questions that are directly related to this inquiry, but I'm trying to start by keeping it simple.

Thanks very much in advance.

Cheers

 

[alansidman]

Re: How to Extract Multiple Sections of Text in a String, Between Two Repeating Words

Will each successive row take on the same characteristics. To ensure a good solution, it would be beneficial to see a few more records to see if there is any consistency.

 

[tyija1995]

Re: How to Extract Multiple Sections of Text in a String, Between Two Repeating Words

Hey, providing the string format follows the above rule try give this a try in cell A2:

=MID($A$1,SEARCH("|",SUBSTITUTE($A$1,"headline:","|",ROW()-1))+10,SEARCH("|",SUBSTITUTE($A$1,"source:","|",ROW()-1))-SEARCH("|",SUBSTITUTE($A$1,"headline:","|",ROW()-1))-12)

Copy this formula down to see successive headlines.

 

[jtakw]

Re: How to Extract Multiple Sections of Text in a String, Between Two Repeating Words

Hi,

Assuming data string in A1...

Use B1 formula copied across if you want results horizontal,
Use A2 formula copied down if you want results vertical:

Book1
	A	B	C	D	E
1	{"date": 5/7/19 headline:"GE Posts Profit" source:"CNBC"}{"date": 5/8/19 headline:"GE Dividend Shrink" source:"MSN"}{"date": 5/9/19 headline:"GE Bankrupt" source:"WSJ"}	GE Posts Profit	GE Dividend Shrink	GE Bankrupt
2	GE Posts Profit
3	GE Dividend Shrink
4	GE Bankrupt
5
Sheet661

Cell Formulas
Range	Formula
B1	=TRIM(SUBSTITUTE(MID(SUBSTITUTE(SUBSTITUTE($A1,"headline:",REPT(" ",LEN($A1))),"source:",REPT(" ",LEN($A1))),(COLUMNS($B1:B1)*2-1)*LEN($A1),LEN($A1)),CHAR(34),""))
A2	=TRIM(SUBSTITUTE(MID(SUBSTITUTE(SUBSTITUTE(A$1,"headline:",REPT(" ",LEN(A$1))),"source:",REPT(" ",LEN(A$1))),(ROWS(A$2:A2)*2-1)*LEN(A$1),LEN(A$1)),CHAR(34),""))

 

[blafarm]

Re: How to Extract Multiple Sections of Text in a String, Between Two Repeating Words

Will each successive row take on the same characteristics. To ensure a good solution, it would be beneficial to see a few more records to see if there is any consistency.

Thank you for your prompt response.

I was trying to keep it simple, but at the bottom of this post is a sample of the kind of text string I am working with.

As you can see, there are more metrics in the string than just the Date, Headline and Source that I presented in my simplified example.

In a perfect world, I would be able to extract the following plurality of metrics in the order they appear within the string:

datetime
headline
source
url

So the resulting column would look like this:

datetime (1st appearance)
headline (1st appearance)
source (1st appearance)
url (1st appearance)
datetime (2nd appearance)
headline (2nd appearance)
source (2nd appearance))
url (2nd appearance)
datetime (3rd appearance)
headline (3rd appearance)
source (3rd appearance)
url (3rd appearance)

However, I can imagine that might be a tall order -- so I thought I'd aim lower and try to extract singular metrics in one formula so that the result looks like this

headline (1st appearance)
headline (2nd appearance)
headline (3rd appearance)

It is worth mentioning that I have tried many times to use various delimiters to split/parse this text into a horizontal sequence cells.
However, there is no singular delimiter I can use that does not occasionally appear in the Headline or Summary, which then causes the cells to contain text fragments.
That's why I tried experimenting with SEARCH -- because I could conceivably extract text between words that would not be found in a Headline or Summary.

Sample Text String:

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[{"datetime":1557328805000 headline:"GE CEO says weaker quarters likely after 'good start' to year" source:"Reuters" url:"https://cloud.iexapis.com/v1/news/article/1e7714c0-93f6-4921-af24-f3122a2e809e" summary:"General Electric Co Chief Executive Larry Culp said on Wednesday the company will likely have weaker quarters in the rest of the year after a surprisingly "good start" in the first quarter." related:"GE" image:"https://cloud.iexapis.com/v1/news/image/1e7714c0-93f6-4921-af24-f3122a2e809e" lang:"en" hasPaywall:false} {"datetime":1557307800000 headline:"GE Investors Gather to Elect Revamped Board Air Gripes" source:"The Wall Street Journal" url:"https://cloud.iexapis.com/v1/news/article/dfb8a33f-9194-493e-9b83-9709dc0d18a9" summary:"General Electric Chairman and CEO Larry Culp who joined the company just a year ago is expected to answer questions from investors at the shareholder meeting in Tarrytown N.Y." related:"GE" image:"https://cloud.iexapis.com/v1/news/image/dfb8a33f-9194-493e-9b83-9709dc0d18a9" lang:"en" hasPaywall:true} {"datetime":1557205509000 headline:"IndiGo is making plans for another aggressive push" source:"The Economic Times India" url:"https://cloud.iexapis.com/v1/news/article/6ffe2450-5e9f-4166-b5f6-a26a79f10908" summary:"by Anurag KotokyIndiGo is in talks with Airbus SE for another large plane order in a sign Asia’s biggest budget carrier has no intention of letting up on a blistering pace of expansion.The Indian airline which adds an aircraft to its fleet every week is in discussions to buy a longer-range version of the European planemaker’s newest narrow-body jet according to Chief Executive Officer Ronojoy Dutta.Existing orders will see IndiGo through the next two years Dutta said in an interview with Bloomberg News Editor-in-Chief John Micklethwait. After that the carrier is considering adding new planes including Airbus’s long-distance A321neo LR and the yet-to-be-released A321 XLR he said.“When we order we’ll order in bulk for sure ” Dutta said in New Delhi in his first interview since becoming CEO of the airline in January. “I can’t give you a tentative number but it will be large. We need longer range.”Buying BigThough Dutta didn’t specify the size of IndiGo’s next purchase it’s likely to be a multi-billion-dollar order based on the company’s history." related:"GE" image:"https://cloud.iexapis.com/v1/news/image/6ffe2450-5e9f-4166-b5f6-a26a79f10908" lang:"en" hasPaywall:false} {"datetime":1557085967000 headline:"General Electric: Larry Culp Is The Man (For The Job)" source:"Seeking Alpha" url:"https://cloud.iexapis.com/v1/news/article/87bdd7f9-1aec-4943-835f-7a436483cede" summary:"General Electric reported strong Q1 2019 results that beat the consensus bottom-line estimate. The stock is up big since General Electric reported Q1 2019 resul" related:"GE" image:"https://cloud.iexapis.com/v1/news/image/87bdd7f9-1aec-4943-835f-7a436483cede" lang:"en" hasPaywall:false} {"datetime":1556912727000 headline:"GE to build 500 MW coal power plant in Kosovo" source:"Seeking Alpha" url:"https://cloud.iexapis.com/v1/news/article/cf2c1bf4-2740-4f80-981b-7a68a8ec327f" summary:"A General Electric-led ([[GE]] +2.1%) consortium is selected to build and equip a new 500 MW coal-fired power plant in Kosovo. The new plant designed to m" related:"GE" image:"https://cloud.iexapis.com/v1/news/image/cf2c1bf4-2740-4f80-981b-7a68a8ec327f" lang:"en" hasPaywall:false} {"datetime":1556901660000 headline:"Good News! General Electric Burned Less Cash in Q1" source:"The Motley Fool" url:"https://cloud.iexapis.com/v1/news/article/492e3f89-1206-4547-a8a8-db086e773abb" summary:"Is there light at the end of the tunnel for the troubled industrial conglomerate?" related:"GE" image:"https://cloud.iexapis.com/v1/news/image/492e3f89-1206-4547-a8a8-db086e773abb" lang:"en" hasPaywall:false} {"datetime":1556893560000 headline:"The Zacks Analyst Blog Highlights: Apple Microsoft General Electric Ecolab and Celgene" source:"Zacks Investment Research" url:"https://cloud.iexapis.com/v1/news/article/739fd73b-1e73-4d85-8e8b-7ced62a4e5ea" summary:"The Zacks Analyst Blog Highlights: Apple Microsoft General Electric Ecolab and Celgene" related:"GE" image:"https://cloud.iexapis.com/v1/news/image/739fd73b-1e73-4d85-8e8b-7ced62a4e5ea" lang:"en" hasPaywall:false} {"datetime":1556889257000 headline:"GE to build Kosovo's new 500 MW coal power plant" source:"Reuters" url:"https://cloud.iexapis.com/v1/news/article/f5e2699b-b92e-4aed-aa90-02b557ce5652" summary:"Kosovo and London-listed power firm ContourGlobal said on Friday they had chosen a consortium of General Electric subsidiaries to build and equip a new 500 megawatt (MW)coal-fired power plant in the Balkan country." related:"GE" image:"https://cloud.iexapis.com/v1/news/image/f5e2699b-b92e-4aed-aa90-02b557ce5652" lang:"en" hasPaywall:false} {"datetime":1556877840000 headline:"Key Takeaways From General Electric's Earnings" source:"The Motley Fool" url:"https://cloud.iexapis.com/v1/news/article/356ca854-67f0-4e1b-a782-4f1986da535f" summary:"The company delivered a solid but unspectacular quarter but that might be just what investors need right now." related:"GE" image:"https://cloud.iexapis.com/v1/news/image/356ca854-67f0-4e1b-a782-4f1986da535f" lang:"en" hasPaywall:false} {"datetime":1556833186000 headline:"General Electric: Still A Mystery" source:"Seeking Alpha" url:"https://cloud.iexapis.com/v1/news/article/2a0c3a5c-32fc-4d40-b8e7-458792672cda" summary:"General Electric released its first quarter reports with a little bit of positive news and a little bit of negative news. As one analyst put it Did this change" related:"GE" image:"https://cloud.iexapis.com/v1/news/image/2a0c3a5c-32fc-4d40-b8e7-458792672cda" lang:"en" hasPaywall:false}]

 

[blafarm]

Re: How to Extract Multiple Sections of Text in a String, Between Two Repeating Words

Hey, providing the string format follows the above rule try give this a try in cell A2:

=MID($A$1,SEARCH("|",SUBSTITUTE($A$1,"headline:","|",ROW()-1))+10,SEARCH("|",SUBSTITUTE($A$1,"source:","|",ROW()-1))-SEARCH("|",SUBSTITUTE($A$1,"headline:","|",ROW()-1))-12)

Copy this formula down to see successive headlines.

Thank you for your prompt reply.

I tried your formula but am getting an error related to the requirement of the pipe argument "|"

That symbol does not appear in my text strings (please see my post above).

Thank you.

 

[blafarm]

Re: How to Extract Multiple Sections of Text in a String, Between Two Repeating Words

Hi,

Assuming data string in A1...

Use B1 formula copied across if you want results horizontal,
Use A2 formula copied down if you want results vertical:

	A	B	C	D	E
{"date": 5/7/19 headline:"GE Posts Profit" source:"CNBC"}{"date": 5/8/19 headline:"GE Dividend Shrink" source:"MSN"}{"date": 5/9/19 headline:"GE Bankrupt" source:"WSJ"}	GE Posts Profit	GE Dividend Shrink	GE Bankrupt
GE Posts Profit
GE Dividend Shrink
GE Bankrupt

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet661​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]B1[/TH]
[TD="align: left"]=TRIM(SUBSTITUTE(MID(SUBSTITUTE(SUBSTITUTE($A1,"headline:",REPT(" ",LEN($A1))),"source:",REPT(" ",LEN($A1))),(COLUMNS($B1:B1)*2-1)*LEN($A1),LEN($A1)),CHAR(34),""))[/TD]
[/TR]
[TR]
[TH]A2[/TH]
[TD="align: left"]=TRIM(SUBSTITUTE(MID(SUBSTITUTE(SUBSTITUTE(A$1,"headline:",REPT(" ",LEN(A$1))),"source:",REPT(" ",LEN(A$1))),(ROWS(A$2:A2)*2-1)*LEN(A$1),LEN(A$1)),CHAR(34),""))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thank you for your prompt reply.

Your ROW formula works perfectly. Thank you very much!
I am having a bit of trouble with COLUMN version -- maybe because I cannot place your formula in Cell B1.
So I placed it in Cell A3 and modified the formula it as follows:

=TRIM(SUBSTITUTE(MID(SUBSTITUTE(SUBSTITUTE(A$1,"headline:",REPT(" ",LEN(A$1))),"source:",REPT(" ",LEN(A$1))),(COLUMNS($A3:A3)*2-1)*LEN(A$1),LEN(A$1)),CHAR(34),""))

When I do this, I do get the first headline in A3 -- but I get #NUM! errors for all the columns to the right.
This isn't a big problem, though, as the ROW version is probably more useful for my application anyway.

However, I do have two follow-up questions if you don't mind:

1. I am working in a Google Sheets environment and when I use your formula against a string that is 3,560 characters long -- it works perfectly.
However, when the string grows to 4,047 characters long, I get a #VALUE error.
And when I hover over a cell with that #VALUE error, Google Sheets gives me an Error message that consists of: "Text result of SUBSTITUTE is longer than the limit of 50000 characters."
Now, I've measured the string length using a variety of tools and I am quite certain this is breaking at only 4,047 characters.
Do you have any idea why this might be happening, and how to overcome it?

2. I posted a real sample of my text string in a post above and mentioned that, in a perfect world, I would like to be able to sequentially extract text segments in the string that exist between four different pairs of repeated boundary words.

Here are the pieces of text and the word pairs that bound them:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Extracted Text[/TD]
[TD]Beginning Boundary[/TD]
[TD]Ending Boundary[/TD]
[/TR]
[TR]
[TD]1557328805000[/TD]
[TD]"datetime":[/TD]
[TD] headline:"[/TD]
[/TR]
[TR]
[TD]GE Posts Profit[/TD]
[TD]headline:"[/TD]
[TD]" source:[/TD]
[/TR]
[TR]
[TD]CNBC[/TD]
[TD]source:"[/TD]
[TD] url:"[/TD]
[/TR]
[TR]
[TD]www.cnbc.com[/TD]
[TD] url:"[/TD]
[TD]" summary:"[/TD]
[/TR]
</tbody>[/TABLE]

And in this perfect world, the resulting row would look like this:

[TABLE="class: grid, width: 150"]
<tbody>[TR]
[TD]1557328805000[/TD]
[/TR]
[TR]
[TD]GE Posts Profit[/TD]
[/TR]
[TR]
[TD]CNBC[/TD]
[/TR]
[TR]
[TD]www.cnbc.com[/TD]
[/TR]
[TR]
[TD]1657328805000[/TD]
[/TR]
[TR]
[TD]GE Dividends Shrink[/TD]
[/TR]
[TR]
[TD]MSN[/TD]
[/TR]
[TR]
[TD]www.msn.com[/TD]
[/TR]
</tbody>[/TABLE]
And so on ...

So, my question is: Is it possible to repeatedly extract those four text segments, that exist between those four different pairs of repeated boundary words?

Thank you once again.

 

[blafarm]

Re: How to Extract Multiple Sections of Text in a String, Between Two Repeating Words

UPDATE:

Well, I've solved all of my problems except this one:

1. I am working in a Google Sheets environment and when I use your formula against a string that is 3,560 characters long -- it works perfectly.
However, when the string grows to 4,047 characters long, I get a #VALUE error.
And when I hover over a cell with that #VALUE error, Google Sheets gives me an Error message that consists of: "Text result of SUBSTITUTE is longer than the limit of 50000 characters."
Now, I've measured the string length using a variety of tools and I am quite certain this is breaking at only 4,047 characters.
Do you have any idea why this might be happening, and how to overcome it?

 

[jtakw]

Re: How to Extract Multiple Sections of Text in a String, Between Two Repeating Words

Hi,

Again assuming your data string in A1, and assuming you start the extraction in A2, going downwards.

Use this updated formula in A2 copied down:

Book1
	A
1	[{"datetime":1557328805000 headline:"GE CEO says weaker quarters likely after 'good start' to year" source:"Reuters" url:"https://cloud.iexapis.com/v1/news/article/1e7714c0-93f6-4921-af24-f3122a2e809e" summary:"General Electric Co Chief Executive Larry Culp said on Wednesday the company will likely have weaker quarters in the rest of the year after a surprisingly "good start" in the first quarter." related:"GE" image:"https://cloud.iexapis.com/v1/news/image/1e7714c0-93f6-4921-af24-f3122a2e809e" lang:"en" hasPaywall:false} {"datetime":1557307800000 headline:"GE Investors Gather to Elect Revamped Board Air Gripes" source:"The Wall Street Journal" url:"https://cloud.iexapis.com/v1/news/article/dfb8a33f-9194-493e-9b83-9709dc0d18a9" summary:"General Electric Chairman and CEO Larry Culp who joined the company just a year ago is expected to answer questions from investors at the shareholder meeting in Tarrytown N.Y." related:"GE" image:"https://cloud.iexapis.com/v1/news/image/dfb8a33f-9194-493e-9b83-9709dc0d18a9" lang:"en" hasPaywall:true} {"datetime":1557205509000 headline:"IndiGo is making plans for another aggressive push" source:"The Economic Times India" url:"https://cloud.iexapis.com/v1/news/article/6ffe2450-5e9f-4166-b5f6-a26a79f10908" summary:"by Anurag KotokyIndiGo is in talks with Airbus SE for another large plane order in a sign Asias biggest budget carrier has no intention of letting up on a blistering pace of expansion.The Indian airline which adds an aircraft to its fleet every week is in discussions to buy a longer-range version of the European planemakers newest narrow-body jet according to Chief Executive Officer Ronojoy Dutta.Existing orders will see IndiGo through the next two years Dutta said in an interview with Bloomberg News Editor-in-Chief John Micklethwait. After that the carrier is considering adding new planes including Airbuss long-distance A321neo LR and the yet-to-be-released A321 XLR he said.When we order well order in bulk for sure Dutta said in New Delhi in his first interview since becoming CEO of the airline in January. I cant give you a tentative number but it will be large. We need longer range.Buying BigThough Dutta didnt specify the size of IndiGos next purchase its likely to be a multi-billion-dollar order based on the companys history." related:"GE" image:"https://cloud.iexapis.com/v1/news/image/6ffe2450-5e9f-4166-b5f6-a26a79f10908" lang:"en" hasPaywall:false} {"datetime":1557085967000 headline:"General Electric: Larry Culp Is The Man (For The Job)" source:"Seeking Alpha" url:"https://cloud.iexapis.com/v1/news/article/87bdd7f9-1aec-4943-835f-7a436483cede" summary:"General Electric reported strong Q1 2019 results that beat the consensus bottom-line estimate. The stock is up big since General Electric reported Q1 2019 resul" related:"GE" image:"https://cloud.iexapis.com/v1/news/image/87bdd7f9-1aec-4943-835f-7a436483cede" lang:"en" hasPaywall:false} {"datetime":1556912727000 headline:"GE to build 500 MW coal power plant in Kosovo" source:"Seeking Alpha" url:"https://cloud.iexapis.com/v1/news/article/cf2c1bf4-2740-4f80-981b-7a68a8ec327f" summary:"A General Electric-led ([[GE]] +2.1%) consortium is selected to build and equip a new 500 MW coal-fired power plant in Kosovo. The new plant designed to m" related:"GE" image:"https://cloud.iexapis.com/v1/news/image/cf2c1bf4-2740-4f80-981b-7a68a8ec327f" lang:"en" hasPaywall:false} {"datetime":1556901660000 headline:"Good News! General Electric Burned Less Cash in Q1" source:"The Motley Fool" url:"https://cloud.iexapis.com/v1/news/article/492e3f89-1206-4547-a8a8-db086e773abb" summary:"Is there light at the end of the tunnel for the troubled industrial conglomerate?" related:"GE" image:"https://cloud.iexapis.com/v1/news/image/492e3f89-1206-4547-a8a8-db086e773abb" lang:"en" hasPaywall:false} {"datetime":1556893560000 headline:"The Zacks Analyst Blog Highlights: Apple Microsoft General Electric Ecolab and Celgene" source:"Zacks Investment Research" url:"https://cloud.iexapis.com/v1/news/article/739fd73b-1e73-4d85-8e8b-7ced62a4e5ea" summary:"The Zacks Analyst Blog Highlights: Apple Microsoft General Electric Ecolab and Celgene" related:"GE" image:"https://cloud.iexapis.com/v1/news/image/739fd73b-1e73-4d85-8e8b-7ced62a4e5ea" lang:"en" hasPaywall:false} {"datetime":1556889257000 headline:"GE to build Kosovo's new 500 MW coal power plant" source:"Reuters" url:"https://cloud.iexapis.com/v1/news/article/f5e2699b-b92e-4aed-aa90-02b557ce5652" summary:"Kosovo and London-listed power firm ContourGlobal said on Friday they had chosen a consortium of General Electric subsidiaries to build and equip a new 500 megawatt (MW)coal-fired power plant in the Balkan country." related:"GE" image:"https://cloud.iexapis.com/v1/news/image/f5e2699b-b92e-4aed-aa90-02b557ce5652" lang:"en" hasPaywall:false} {"datetime":1556877840000 headline:"Key Takeaways From General Electric's Earnings" source:"The Motley Fool" url:"https://cloud.iexapis.com/v1/news/article/356ca854-67f0-4e1b-a782-4f1986da535f" summary:"The company delivered a solid but unspectacular quarter but that might be just what investors need right now." related:"GE" image:"https://cloud.iexapis.com/v1/news/image/356ca854-67f0-4e1b-a782-4f1986da535f" lang:"en" hasPaywall:false} {"datetime":1556833186000 headline:"General Electric: Still A Mystery" source:"Seeking Alpha" url:"https://cloud.iexapis.com/v1/news/article/2a0c3a5c-32fc-4d40-b8e7-458792672cda" summary:"General Electric released its first quarter reports with a little bit of positive news and a little bit of negative news. As one analyst put it Did this change" related:"GE" image:"https://cloud.iexapis.com/v1/news/image/2a0c3a5c-32fc-4d40-b8e7-458792672cda" lang:"en" hasPaywall:false}]
2	1557328805000
3	GE CEO says weaker quarters likely after 'good start' to year
4	Reuters
5	https//cloud.iexapis.com/v1/news/article/1e7714c0-93f6-4921-af24-f3122a2e809e
6	1557307800000
7	GE Investors Gather to Elect Revamped Board Air Gripes
8	The Wall Street Journal
9	https//cloud.iexapis.com/v1/news/article/dfb8a33f-9194-493e-9b83-9709dc0d18a9
10	1557205509000
11	IndiGo is making plans for another aggressive push
12	The Economic Times India
13	https//cloud.iexapis.com/v1/news/article/6ffe2450-5e9f-4166-b5f6-a26a79f10908
14	1557085967000
15	General Electric Larry Culp Is The Man (For The Job)
16	Seeking Alpha
17	https//cloud.iexapis.com/v1/news/article/87bdd7f9-1aec-4943-835f-7a436483cede
18	1556912727000
19	GE to build 500 MW coal power plant in Kosovo
20	Seeking Alpha
21	https//cloud.iexapis.com/v1/news/article/cf2c1bf4-2740-4f80-981b-7a68a8ec327f
22	1556901660000
23	Good News! General Electric Burned Less Cash in Q1
24	The Motley Fool
25	https//cloud.iexapis.com/v1/news/article/492e3f89-1206-4547-a8a8-db086e773abb
26	1556893560000
27	The Zacks Analyst Blog Highlights Apple Microsoft General Electric Ecolab and Celgene
28	Zacks Investment Research
29	https//cloud.iexapis.com/v1/news/article/739fd73b-1e73-4d85-8e8b-7ced62a4e5ea
30	1556889257000
31	GE to build Kosovo's new 500 MW coal power plant
32	Reuters
33	https//cloud.iexapis.com/v1/news/article/f5e2699b-b92e-4aed-aa90-02b557ce5652
34	1556877840000
35	Key Takeaways From General Electric's Earnings
36	The Motley Fool
37	https//cloud.iexapis.com/v1/news/article/356ca854-67f0-4e1b-a782-4f1986da535f
38	1556833186000
39	General Electric Still A Mystery
40	Seeking Alpha
41	https//cloud.iexapis.com/v1/news/article/2a0c3a5c-32fc-4d40-b8e7-458792672cda
42
Sheet661 (2)

Cell Formulas
Range	Formula
A2	=SUBSTITUTE(SUBSTITUTE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(A$1,CHOOSE(ROUND(MOD(ROWS(A$2:A2)-0.5,4),0),"datetime","headline:","source:","url:"),REPT(" ",LEN(A$1)),INT((ROWS(A$2:A2)+3)/4)),CHOOSE(ROUND(MOD(ROWS(A$2:A2)-0.5,4),0),"headline:","source:","url:","summary:"),REPT(" ",LEN(A$1)),INT((ROWS(A$2:A2)+3)/4)),LEN(A$1),LEN(A$1))),CHAR(34),""),":","")

 

[blafarm]

Re: How to Extract Multiple Sections of Text in a String, Between Two Repeating Words

Hi,

Again assuming your data string in A1, and assuming you start the extraction in A2, going downwards.

Use this updated formula in A2 copied down:

	A
[{"datetime":1557328805000 headline:"GE CEO says weaker quarters likely after 'good start' to year" source:"Reuters" url:"https://cloud.iexapis.com/v1/news/article/1e7714c0-93f6-4921-af24-f3122a2e809e" summary:"General Electric Co Chief Executive Larry Culp said on Wednesday the company will likely have weaker quarters in the rest of the year after a surprisingly "good start" in the first quarter." related:"GE" image:"https://cloud.iexapis.com/v1/news/image/1e7714c0-93f6-4921-af24-f3122a2e809e" lang:"en" hasPaywall:false} {"datetime":1557307800000 headline:"GE Investors Gather to Elect Revamped Board Air Gripes" source:"The Wall Street Journal" url:"https://cloud.iexapis.com/v1/news/article/dfb8a33f-9194-493e-9b83-9709dc0d18a9" summary:"General Electric Chairman and CEO Larry Culp who joined the company just a year ago is expected to answer questions from investors at the shareholder meeting in Tarrytown N.Y." related:"GE" image:"https://cloud.iexapis.com/v1/news/image/dfb8a33f-9194-493e-9b83-9709dc0d18a9" lang:"en" hasPaywall:true} {"datetime":1557205509000 headline:"IndiGo is making plans for another aggressive push" source:"The Economic Times India" url:"https://cloud.iexapis.com/v1/news/article/6ffe2450-5e9f-4166-b5f6-a26a79f10908" summary:"by Anurag KotokyIndiGo is in talks with Airbus SE for another large plane order in a sign Asia’s biggest budget carrier has no intention of letting up on a blistering pace of expansion.The Indian airline which adds an aircraft to its fleet every week is in discussions to buy a longer-range version of the European planemaker’s newest narrow-body jet according to Chief Executive Officer Ronojoy Dutta.Existing orders will see IndiGo through the next two years Dutta said in an interview with Bloomberg News Editor-in-Chief John Micklethwait. After that the carrier is considering adding new planes including Airbus’s long-distance A321neo LR and the yet-to-be-released A321 XLR he said.“When we order we’ll order in bulk for sure ” Dutta said in New Delhi in his first interview since becoming CEO of the airline in January. “I can’t give you a tentative number but it will be large. We need longer range.”Buying BigThough Dutta didn’t specify the size of IndiGo’s next purchase it’s likely to be a multi-billion-dollar order based on the company’s history." related:"GE" image:"https://cloud.iexapis.com/v1/news/image/6ffe2450-5e9f-4166-b5f6-a26a79f10908" lang:"en" hasPaywall:false} {"datetime":1557085967000 headline:"General Electric: Larry Culp Is The Man (For The Job)" source:"Seeking Alpha" url:"https://cloud.iexapis.com/v1/news/article/87bdd7f9-1aec-4943-835f-7a436483cede" summary:"General Electric reported strong Q1 2019 results that beat the consensus bottom-line estimate. The stock is up big since General Electric reported Q1 2019 resul" related:"GE" image:"https://cloud.iexapis.com/v1/news/image/87bdd7f9-1aec-4943-835f-7a436483cede" lang:"en" hasPaywall:false} {"datetime":1556912727000 headline:"GE to build 500 MW coal power plant in Kosovo" source:"Seeking Alpha" url:"https://cloud.iexapis.com/v1/news/article/cf2c1bf4-2740-4f80-981b-7a68a8ec327f" summary:"A General Electric-led ([[GE]] +2.1%) consortium is selected to build and equip a new 500 MW coal-fired power plant in Kosovo. The new plant designed to m" related:"GE" image:"https://cloud.iexapis.com/v1/news/image/cf2c1bf4-2740-4f80-981b-7a68a8ec327f" lang:"en" hasPaywall:false} {"datetime":1556901660000 headline:"Good News! General Electric Burned Less Cash in Q1" source:"The Motley Fool" url:"https://cloud.iexapis.com/v1/news/article/492e3f89-1206-4547-a8a8-db086e773abb" summary:"Is there light at the end of the tunnel for the troubled industrial conglomerate?" related:"GE" image:"https://cloud.iexapis.com/v1/news/image/492e3f89-1206-4547-a8a8-db086e773abb" lang:"en" hasPaywall:false} {"datetime":1556893560000 headline:"The Zacks Analyst Blog Highlights: Apple Microsoft General Electric Ecolab and Celgene" source:"Zacks Investment Research" url:"https://cloud.iexapis.com/v1/news/article/739fd73b-1e73-4d85-8e8b-7ced62a4e5ea" summary:"The Zacks Analyst Blog Highlights: Apple Microsoft General Electric Ecolab and Celgene" related:"GE" image:"https://cloud.iexapis.com/v1/news/image/739fd73b-1e73-4d85-8e8b-7ced62a4e5ea" lang:"en" hasPaywall:false} {"datetime":1556889257000 headline:"GE to build Kosovo's new 500 MW coal power plant" source:"Reuters" url:"https://cloud.iexapis.com/v1/news/article/f5e2699b-b92e-4aed-aa90-02b557ce5652" summary:"Kosovo and London-listed power firm ContourGlobal said on Friday they had chosen a consortium of General Electric subsidiaries to build and equip a new 500 megawatt (MW)coal-fired power plant in the Balkan country." related:"GE" image:"https://cloud.iexapis.com/v1/news/image/f5e2699b-b92e-4aed-aa90-02b557ce5652" lang:"en" hasPaywall:false} {"datetime":1556877840000 headline:"Key Takeaways From General Electric's Earnings" source:"The Motley Fool" url:"https://cloud.iexapis.com/v1/news/article/356ca854-67f0-4e1b-a782-4f1986da535f" summary:"The company delivered a solid but unspectacular quarter but that might be just what investors need right now." related:"GE" image:"https://cloud.iexapis.com/v1/news/image/356ca854-67f0-4e1b-a782-4f1986da535f" lang:"en" hasPaywall:false} {"datetime":1556833186000 headline:"General Electric: Still A Mystery" source:"Seeking Alpha" url:"https://cloud.iexapis.com/v1/news/article/2a0c3a5c-32fc-4d40-b8e7-458792672cda" summary:"General Electric released its first quarter reports with a little bit of positive news and a little bit of negative news. As one analyst put it Did this change" related:"GE" image:"https://cloud.iexapis.com/v1/news/image/2a0c3a5c-32fc-4d40-b8e7-458792672cda" lang:"en" hasPaywall:false}]
1557328805000
GE CEO says weaker quarters likely after 'good start' to year
Reuters
https//cloud.iexapis.com/v1/news/article/1e7714c0-93f6-4921-af24-f3122a2e809e
1557307800000
GE Investors Gather to Elect Revamped Board Air Gripes
The Wall Street Journal
https//cloud.iexapis.com/v1/news/article/dfb8a33f-9194-493e-9b83-9709dc0d18a9
1557205509000
IndiGo is making plans for another aggressive push
The Economic Times India
https//cloud.iexapis.com/v1/news/article/6ffe2450-5e9f-4166-b5f6-a26a79f10908
1557085967000
General Electric Larry Culp Is The Man (For The Job)
Seeking Alpha
https//cloud.iexapis.com/v1/news/article/87bdd7f9-1aec-4943-835f-7a436483cede
1556912727000
GE to build 500 MW coal power plant in Kosovo
Seeking Alpha
https//cloud.iexapis.com/v1/news/article/cf2c1bf4-2740-4f80-981b-7a68a8ec327f
1556901660000
Good News! General Electric Burned Less Cash in Q1
The Motley Fool
https//cloud.iexapis.com/v1/news/article/492e3f89-1206-4547-a8a8-db086e773abb
1556893560000
The Zacks Analyst Blog Highlights Apple Microsoft General Electric Ecolab and Celgene
Zacks Investment Research
https//cloud.iexapis.com/v1/news/article/739fd73b-1e73-4d85-8e8b-7ced62a4e5ea
1556889257000
GE to build Kosovo's new 500 MW coal power plant
Reuters
https//cloud.iexapis.com/v1/news/article/f5e2699b-b92e-4aed-aa90-02b557ce5652
1556877840000
Key Takeaways From General Electric's Earnings
The Motley Fool
https//cloud.iexapis.com/v1/news/article/356ca854-67f0-4e1b-a782-4f1986da535f
1556833186000
General Electric Still A Mystery
Seeking Alpha
https//cloud.iexapis.com/v1/news/article/2a0c3a5c-32fc-4d40-b8e7-458792672cda

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]13[/TD]

[TD="align: center"]14[/TD]

[TD="align: center"]15[/TD]

[TD="align: center"]16[/TD]

[TD="align: center"]17[/TD]

[TD="align: center"]18[/TD]

[TD="align: center"]19[/TD]

[TD="align: center"]20[/TD]

[TD="align: center"]21[/TD]

[TD="align: center"]22[/TD]

[TD="align: center"]23[/TD]

[TD="align: center"]24[/TD]

[TD="align: center"]25[/TD]

[TD="align: center"]26[/TD]

[TD="align: center"]27[/TD]

[TD="align: center"]28[/TD]

[TD="align: center"]29[/TD]

[TD="align: center"]30[/TD]

[TD="align: center"]31[/TD]

[TD="align: center"]32[/TD]

[TD="align: center"]33[/TD]

[TD="align: center"]34[/TD]

[TD="align: center"]35[/TD]

[TD="align: center"]36[/TD]

[TD="align: center"]37[/TD]

[TD="align: center"]38[/TD]

[TD="align: center"]39[/TD]

[TD="align: center"]40[/TD]

[TD="align: center"]41[/TD]

[TD="align: center"]42[/TD]

</tbody>

Sheet661 (2)​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A2[/TH]
[TD="align: left"]=SUBSTITUTE(SUBSTITUTE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(A$1,CHOOSE(ROUND(MOD(ROWS(A$2:A2)-0.5,4),0),"datetime","headline:","source:","url:"),REPT(" ",LEN(A$1)),INT((ROWS(A$2:A2)+3)/4)),CHOOSE(ROUND(MOD(ROWS(A$2:A2)-0.5,4),0),"headline:","source:","url:","summary:"),REPT(" ",LEN(A$1)),INT((ROWS(A$2:A2)+3)/4)),LEN(A$1),LEN(A$1))),CHAR(34),""),":","")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thank you very much jtakw!

You are clearly very talented -- and very generous with your time.

Any thoughts on the "Text result of SUBSTITUTE is longer than the limit of 50000 characters" error described above?

Thanks again.