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

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Re: How to Extract Multiple Sections of Text in a String, Between Two Repeating Words

This works for your sample text string in Post # 19

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;">4332</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">[{"datetime":1557439398000,"headline":"The Little-Known Truth About The Carbon Footprint For The Tesla Model 3 And Other BEVs","source":"Seeking Alpha","url":"https://cloud.iexapis.com/v1/news/article/4e3cfd53-00e3-402a-89a7-fabc5cde087b","summary":"No summary available.","related":"TSLA,TTM","image":"https://cloud.iexapis.com/v1/news/image/4e3cfd53-00e3-402a-89a7-fabc5cde087b","lang":"en","hasPaywall":false},{"datetime":1557431760000,"headline":"Why Sunrun Stock Dropped 6% Today","source":"The Motley Fool","url":"https://cloud.iexapis.com/v1/news/article/8c733a8b-bf9e-4fa2-ab17-a0bf75a59e4e","summary":"Weak guidance (and competition from Tesla?) eclipse good news on Sunrun sales.","related":"TSLA","image":"https://cloud.iexapis.com/v1/news/image/8c733a8b-bf9e-4fa2-ab17-a0bf75a59e4e","lang":"en","hasPaywall":false},{"datetime":1557431040000,"headline":"When GM Sold a Plant to an Electric-Vehicle Startup...Named Tesla","source":"Barron's","url":"https://cloud.iexapis.com/v1/news/article/bb5a1634-9477-400c-aa9c-3f26afb55490","summary":"Workhorse is buying an idled car plant from General Motors. This isn’t the first time GM has sold a plant cheap to an upstart maker of electric vehicles.","related":"TSLA","image":"https://cloud.iexapis.com/v1/news/image/bb5a1634-9477-400c-aa9c-3f26afb55490","lang":"en","hasPaywall":true},{"datetime":1557426766000,"headline":"Tesla Battery Swap “Enron trading desk all over again”","source":"ValueWalk","url":"https://cloud.iexapis.com/v1/news/article/b9767020-bdbc-453c-9d91-719cceb05b79","summary":"Whitney Tilson’s email to investors discussing Glenn’s wise words on Tesla Inc (NASDAQ:TSLA); comments; Motor Mouths article. 1) Glenn with some wise words: Q1 hedge fund letters, conference, scoops etc Frankly, most of the people, many bears included, have become […] The post Tesla Battery Swap “Enron trading desk all over again” appeared first on ValueWalk .","related":"TSLA","image":"https://cloud.iexapis.com/v1/news/image/b9767020-bdbc-453c-9d91-719cceb05b79","lang":"en","hasPaywall":false},{"datetime":1557416228000,"headline":"Hilary Duff and boyfriend Matthew Koma are engaged: 'He asked me to be his wife'","source":"Business Insider","url":"https://cloud.iexapis.com/v1/news/article/1ac8113a-3028-45f4-bba2-674d1905b62d","summary":"On Thursday, Hilary Duff and Matthew Koma revealed that they were engaged. " He asked me to be his wife," Duff captioned two images on Instagram , one of which showed her sparkling ring. The couple has been linked since 2017 and welcomed a baby girl named Banks Violet Bair in October 2018 . Visit INSIDER's homepage for more stories. Hilary Duff and boyfriend Matthew Koma are engaged. The 31-year-old "Younger" star revealed the milestone with an Instagram post that was shared on Thursday. " He asked me to be his wife," Duff captioned two images. One photo showed her, Koma (whose real name is Matthew Bair), and the engagement ring while the second one showed the couple kissing. He asked me to be his wife?? A post shared by Hilary Duff (@hilaryduff) on May 9, 2019 at 7:20am PDT on May 9, 2019 at 7:20am PDT Koma, a musician who's part of a band called Winnetka Bowling League, shared the same images on his Instagram and wrote: " I asked my best friend to marry me." Read more : Hilary Duff says she struggles to ignore mom-shaming on Instagram: 'Bullying seems to be at an all-time high right now' It's unclear when Duff and Koma started dating, but they have been linked since early 2017.","related":"TSLA","image":"https://cloud.iexapis.com/v1/news/image/1ac8113a-3028-45f4-bba2-674d1905b62d","lang":"en","hasPaywall":false},{"datetime":1557416038000,"headline":"Intel's self-driving leader on why Uber, Lyft and Tesla are in a robo-taxi race for their lives","source":"CNBC","url":"https://cloud.iexapis.com/v1/news/article/20f34de1-d57c-46ef-bd5b-18ec018aaa53","summary":"A leading autonomous-driving executive from Mobileye, a company Intel bought for $15 billion, explains why Lyft, Uber, Tesla and any other company seeking a robo-taxi future is in a race to bring down costs by as much as 50%. And then there's the safety issues of teaching AI to drive without a threat to life.","related":"TSLA","image":"https://cloud.iexapis.com/v1/news/image/20f34de1-d57c-46ef-bd5b-18ec018aaa53","lang":"en","hasPaywall":false}]</td></tr></tbody></table><p style="width:9.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet661 (4)</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A1</th><td style="text-align:left">=LEN(<font color="Blue">A2</font>)</td></tr></tbody></table></td></tr></table><br />

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">1557439398000</td><td style=";">The Little-Known Truth About The Carbon Footprint For The Tesla Model 3 And Other BEVs</td><td style=";">Seeking Alpha</td><td style=";">https//cloud.iexapis.com/v1/news/article/4e3cfd53-00e3-402a-89a7-fabc5cde087b</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">1557431760000</td><td style=";">Why Sunrun Stock Dropped 6% Today</td><td style=";">The Motley Fool</td><td style=";">https//cloud.iexapis.com/v1/news/article/8c733a8b-bf9e-4fa2-ab17-a0bf75a59e4e</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">1557431040000</td><td style=";">When GM Sold a Plant to an Electric-Vehicle Startup...Named Tesla</td><td style=";">Barron's</td><td style=";">https//cloud.iexapis.com/v1/news/article/bb5a1634-9477-400c-aa9c-3f26afb55490</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">1557426766000</td><td style=";">Tesla Battery Swap “Enron trading desk all over again”</td><td style=";">ValueWalk</td><td style=";">https//cloud.iexapis.com/v1/news/article/b9767020-bdbc-453c-9d91-719cceb05b79</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">1557416228000</td><td style=";">Hilary Duff and boyfriend Matthew Koma are engaged 'He asked me to be his wife'</td><td style=";">Business Insider</td><td style=";">https//cloud.iexapis.com/v1/news/article/1ac8113a-3028-45f4-bba2-674d1905b62d</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">1557416038000</td><td style=";">Intel's self-driving leader on why Uber Lyft and Tesla are in a robo-taxi race for their lives</td><td style=";">CNBC</td><td style=";">https//cloud.iexapis.com/v1/news/article/20f34de1-d57c-46ef-bd5b-18ec018aaa53</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr></tbody></table><p style="width:9.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet661 (4)</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A5</th><td style="text-align:left">=IF(<font color="Blue">COLUMNS(<font color="Red">$A5:A5</font>)>4,"",SUBSTITUTE(<font color="Red">SUBSTITUTE(<font color="Green">SUBSTITUTE(<font color="Purple">SUBSTITUTE(<font color="Teal">TRIM(<font color="#FF00FF">MID(<font color="Navy">SUBSTITUTE(<font color="Blue">SUBSTITUTE(<font color="Red">$A$2,CHOOSE(<font color="Green">COLUMNS(<font color="Purple">$A5:A5</font>),"""datetime""","""headline""","""source""","""url"""</font>),REPT(<font color="Green">" ",LEN(<font color="Purple">$A$2</font>)</font>),ROWS(<font color="Green">A$5:A5</font>)</font>),CHOOSE(<font color="Red">COLUMNS(<font color="Green">$A5:A5</font>),"""headline""","""source""","""url""","""summary"""</font>),REPT(<font color="Red">" ",LEN(<font color="Green">$A$2</font>)</font>),ROWS(<font color="Red">A$5:A5</font>)</font>),LEN(<font color="Blue">$A$2</font>),LEN(<font color="Blue">$A$2</font>)</font>)</font>),CHAR(<font color="#FF00FF">34</font>),""</font>),":",""</font>),",",""</font>),"]",""</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

Assuming text string in A2, A5 formula copied Across to Column D and Down as far as needed.
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

blafarm

Board Regular
Joined
Oct 14, 2018
Messages
59
Re: How to Extract Multiple Sections of Text in a String, Between Two Repeating Words

This works for your sample text string in Post # 19


ABCDE
3
4
51557439398000The Little-Known Truth About The Carbon Footprint For The Tesla Model 3 And Other BEVsSeeking Alphahttps//cloud.iexapis.com/v1/news/article/4e3cfd53-00e3-402a-89a7-fabc5cde087b
61557431760000Why Sunrun Stock Dropped 6% TodayThe Motley Foolhttps//cloud.iexapis.com/v1/news/article/8c733a8b-bf9e-4fa2-ab17-a0bf75a59e4e
71557431040000When GM Sold a Plant to an Electric-Vehicle Startup...Named TeslaBarron'shttps//cloud.iexapis.com/v1/news/article/bb5a1634-9477-400c-aa9c-3f26afb55490
81557426766000Tesla Battery Swap “Enron trading desk all over again”ValueWalkhttps//cloud.iexapis.com/v1/news/article/b9767020-bdbc-453c-9d91-719cceb05b79
91557416228000Hilary Duff and boyfriend Matthew Koma are engaged 'He asked me to be his wife'Business Insiderhttps//cloud.iexapis.com/v1/news/article/1ac8113a-3028-45f4-bba2-674d1905b62d
101557416038000Intel's self-driving leader on why Uber Lyft and Tesla are in a robo-taxi race for their livesCNBChttps//cloud.iexapis.com/v1/news/article/20f34de1-d57c-46ef-bd5b-18ec018aaa53
11

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet661 (4)

Worksheet Formulas
CellFormula
A5=IF(COLUMNS($A5:A5)>4,"",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(MID(SUBSTITUTE(SUBSTITUTE($A$2,CHOOSE(COLUMNS($A5:A5),"""datetime""","""headline""","""source""","""url"""),REPT(" ",LEN($A$2)),ROWS(A$5:A5)),CHOOSE(COLUMNS($A5:A5),"""headline""","""source""","""url""","""summary"""),REPT(" ",LEN($A$2)),ROWS(A$5:A5)),LEN($A$2),LEN($A$2))),CHAR(34),""),":",""),",",""),"]",""))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



Assuming text string in A2, A5 formula copied Across to Column D and Down as far as needed.

Hi jtakw,

Thanks very much for this.
I tried many options but would have never arrived at that.

One small problem, the formula is deleting the colon :)) in the URL after "https" and before "//"
I've tried modifying your formula with no success.

If it is not possible to fix this -- I will figure out a workaround.
Please let me know when you get a chance.
And, thank you very much.
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Re: How to Extract Multiple Sections of Text in a String, Between Two Repeating Words

Sorry, overlooked that part...

Use this for your Google Sheets version:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;">5767</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td 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}] </td></tr></tbody></table><p style="width:9.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet661 (3)</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A1</th><td style="text-align:left">=LEN(<font color="Blue">A2</font>)</td></tr></tbody></table></td></tr></table><br />

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">1557328805000</td><td style=";">GE CEO says weaker quarters likely after 'good start' to year</td><td style=";">Reuters</td><td style=";">https://cloud.iexapis.com/v1/news/article/1e7714c0-93f6-4921-af24-f3122a2e809e</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">1557307800000</td><td style=";">GE Investors Gather to Elect Revamped Board Air Gripes</td><td style=";">The Wall Street Journal</td><td style=";">https://cloud.iexapis.com/v1/news/article/dfb8a33f-9194-493e-9b83-9709dc0d18a9</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">1557205509000</td><td style=";">IndiGo is making plans for another aggressive push</td><td style=";">The Economic Times India</td><td style=";">https://cloud.iexapis.com/v1/news/article/6ffe2450-5e9f-4166-b5f6-a26a79f10908</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">1557085967000</td><td style=";">General Electric: Larry Culp Is The Man (For The Job)</td><td style=";">Seeking Alpha</td><td style=";">https://cloud.iexapis.com/v1/news/article/87bdd7f9-1aec-4943-835f-7a436483cede</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">1556912727000</td><td style=";">GE to build 500 MW coal power plant in Kosovo</td><td style=";">Seeking Alpha</td><td style=";">https://cloud.iexapis.com/v1/news/article/cf2c1bf4-2740-4f80-981b-7a68a8ec327f</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">1556901660000</td><td style=";">Good News! General Electric Burned Less Cash in Q1</td><td style=";">The Motley Fool</td><td style=";">https://cloud.iexapis.com/v1/news/article/492e3f89-1206-4547-a8a8-db086e773abb</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">1556893560000</td><td style=";">The Zacks Analyst Blog Highlights: Apple Microsoft General Electric Ecolab and Celgene</td><td style=";">Zacks Investment Research</td><td style=";">https://cloud.iexapis.com/v1/news/article/739fd73b-1e73-4d85-8e8b-7ced62a4e5ea</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">1556889257000</td><td style=";">GE to build Kosovo's new 500 MW coal power plant</td><td style=";">Reuters</td><td style=";">https://cloud.iexapis.com/v1/news/article/f5e2699b-b92e-4aed-aa90-02b557ce5652</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">1556877840000</td><td style=";">Key Takeaways From General Electric's Earnings</td><td style=";">The Motley Fool</td><td style=";">https://cloud.iexapis.com/v1/news/article/356ca854-67f0-4e1b-a782-4f1986da535f</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">1556833186000</td><td style=";">General Electric: Still A Mystery</td><td style=";">Seeking Alpha</td><td style=";">https://cloud.iexapis.com/v1/news/article/2a0c3a5c-32fc-4d40-b8e7-458792672cda</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr></tbody></table><p style="width:9.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet661 (3)</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A5</th><td style="text-align:left">=IF(<font color="Blue">COLUMNS(<font color="Red">$A5:A5</font>)>4,"",SUBSTITUTE(<font color="Red">SUBSTITUTE(<font color="Green">TRIM(<font color="Purple">MID(<font color="Teal">SUBSTITUTE(<font color="#FF00FF">SUBSTITUTE(<font color="Navy">$A$2,CHOOSE(<font color="Blue">COLUMNS(<font color="Red">$A5:A5</font>),"datetime","headline:","source:","url:"</font>),REPT(<font color="Blue">" ",LEN(<font color="Red">$A$2</font>)</font>),ROWS(<font color="Blue">A$5:A5</font>)</font>),CHOOSE(<font color="Navy">COLUMNS(<font color="Blue">$A5:A5</font>),"headline:","source:","url:","summary:"</font>),REPT(<font color="Navy">" ",LEN(<font color="Blue">$A$2</font>)</font>),ROWS(<font color="Navy">A$5:A5</font>)</font>),LEN(<font color="#FF00FF">$A$2</font>),LEN(<font color="#FF00FF">$A$2</font>)</font>)</font>),CHAR(<font color="Purple">34</font>)&":",""</font>),CHAR(<font color="Green">34</font>),""</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Re: How to Extract Multiple Sections of Text in a String, Between Two Repeating Words

Use this for your Excel version:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;">4332</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">[{"datetime":1557439398000,"headline":"The Little-Known Truth About The Carbon Footprint For The Tesla Model 3 And Other BEVs","source":"Seeking Alpha","url":"https://cloud.iexapis.com/v1/news/article/4e3cfd53-00e3-402a-89a7-fabc5cde087b","summary":"No summary available.","related":"TSLA,TTM","image":"https://cloud.iexapis.com/v1/news/image/4e3cfd53-00e3-402a-89a7-fabc5cde087b","lang":"en","hasPaywall":false},{"datetime":1557431760000,"headline":"Why Sunrun Stock Dropped 6% Today","source":"The Motley Fool","url":"https://cloud.iexapis.com/v1/news/article/8c733a8b-bf9e-4fa2-ab17-a0bf75a59e4e","summary":"Weak guidance (and competition from Tesla?) eclipse good news on Sunrun sales.","related":"TSLA","image":"https://cloud.iexapis.com/v1/news/image/8c733a8b-bf9e-4fa2-ab17-a0bf75a59e4e","lang":"en","hasPaywall":false},{"datetime":1557431040000,"headline":"When GM Sold a Plant to an Electric-Vehicle Startup...Named Tesla","source":"Barron's","url":"https://cloud.iexapis.com/v1/news/article/bb5a1634-9477-400c-aa9c-3f26afb55490","summary":"Workhorse is buying an idled car plant from General Motors. This isn’t the first time GM has sold a plant cheap to an upstart maker of electric vehicles.","related":"TSLA","image":"https://cloud.iexapis.com/v1/news/image/bb5a1634-9477-400c-aa9c-3f26afb55490","lang":"en","hasPaywall":true},{"datetime":1557426766000,"headline":"Tesla Battery Swap “Enron trading desk all over again”","source":"ValueWalk","url":"https://cloud.iexapis.com/v1/news/article/b9767020-bdbc-453c-9d91-719cceb05b79","summary":"Whitney Tilson’s email to investors discussing Glenn’s wise words on Tesla Inc (NASDAQ:TSLA); comments; Motor Mouths article. 1) Glenn with some wise words: Q1 hedge fund letters, conference, scoops etc Frankly, most of the people, many bears included, have become […] The post Tesla Battery Swap “Enron trading desk all over again” appeared first on ValueWalk .","related":"TSLA","image":"https://cloud.iexapis.com/v1/news/image/b9767020-bdbc-453c-9d91-719cceb05b79","lang":"en","hasPaywall":false},{"datetime":1557416228000,"headline":"Hilary Duff and boyfriend Matthew Koma are engaged: 'He asked me to be his wife'","source":"Business Insider","url":"https://cloud.iexapis.com/v1/news/article/1ac8113a-3028-45f4-bba2-674d1905b62d","summary":"On Thursday, Hilary Duff and Matthew Koma revealed that they were engaged. " He asked me to be his wife," Duff captioned two images on Instagram , one of which showed her sparkling ring. The couple has been linked since 2017 and welcomed a baby girl named Banks Violet Bair in October 2018 . Visit INSIDER's homepage for more stories. Hilary Duff and boyfriend Matthew Koma are engaged. The 31-year-old "Younger" star revealed the milestone with an Instagram post that was shared on Thursday. " He asked me to be his wife," Duff captioned two images. One photo showed her, Koma (whose real name is Matthew Bair), and the engagement ring while the second one showed the couple kissing. He asked me to be his wife?? A post shared by Hilary Duff (@hilaryduff) on May 9, 2019 at 7:20am PDT on May 9, 2019 at 7:20am PDT Koma, a musician who's part of a band called Winnetka Bowling League, shared the same images on his Instagram and wrote: " I asked my best friend to marry me." Read more : Hilary Duff says she struggles to ignore mom-shaming on Instagram: 'Bullying seems to be at an all-time high right now' It's unclear when Duff and Koma started dating, but they have been linked since early 2017.","related":"TSLA","image":"https://cloud.iexapis.com/v1/news/image/1ac8113a-3028-45f4-bba2-674d1905b62d","lang":"en","hasPaywall":false},{"datetime":1557416038000,"headline":"Intel's self-driving leader on why Uber, Lyft and Tesla are in a robo-taxi race for their lives","source":"CNBC","url":"https://cloud.iexapis.com/v1/news/article/20f34de1-d57c-46ef-bd5b-18ec018aaa53","summary":"A leading autonomous-driving executive from Mobileye, a company Intel bought for $15 billion, explains why Lyft, Uber, Tesla and any other company seeking a robo-taxi future is in a race to bring down costs by as much as 50%. And then there's the safety issues of teaching AI to drive without a threat to life.","related":"TSLA","image":"https://cloud.iexapis.com/v1/news/image/20f34de1-d57c-46ef-bd5b-18ec018aaa53","lang":"en","hasPaywall":false}]</td></tr></tbody></table><p style="width:9.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet661 (4)</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A1</th><td style="text-align:left">=LEN(<font color="Blue">A2</font>)</td></tr></tbody></table></td></tr></table><br />

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">1557439398000</td><td style=";">The Little-Known Truth About The Carbon Footprint For The Tesla Model 3 And Other BEVs</td><td style=";">Seeking Alpha</td><td style=";">https://cloud.iexapis.com/v1/news/article/4e3cfd53-00e3-402a-89a7-fabc5cde087b</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">1557431760000</td><td style=";">Why Sunrun Stock Dropped 6% Today</td><td style=";">The Motley Fool</td><td style=";">https://cloud.iexapis.com/v1/news/article/8c733a8b-bf9e-4fa2-ab17-a0bf75a59e4e</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">1557431040000</td><td style=";">When GM Sold a Plant to an Electric-Vehicle Startup...Named Tesla</td><td style=";">Barron's</td><td style=";">https://cloud.iexapis.com/v1/news/article/bb5a1634-9477-400c-aa9c-3f26afb55490</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">1557426766000</td><td style=";">Tesla Battery Swap “Enron trading desk all over again”</td><td style=";">ValueWalk</td><td style=";">https://cloud.iexapis.com/v1/news/article/b9767020-bdbc-453c-9d91-719cceb05b79</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">1557416228000</td><td style=";">Hilary Duff and boyfriend Matthew Koma are engaged: 'He asked me to be his wife'</td><td style=";">Business Insider</td><td style=";">https://cloud.iexapis.com/v1/news/article/1ac8113a-3028-45f4-bba2-674d1905b62d</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">1557416038000</td><td style=";">Intel's self-driving leader on why Uber Lyft and Tesla are in a robo-taxi race for their lives</td><td style=";">CNBC</td><td style=";">https://cloud.iexapis.com/v1/news/article/20f34de1-d57c-46ef-bd5b-18ec018aaa53</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr></tbody></table><p style="width:9.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet661 (4)</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A5</th><td style="text-align:left">=IF(<font color="Blue">COLUMNS(<font color="Red">$A5:A5</font>)>4,"",SUBSTITUTE(<font color="Red">SUBSTITUTE(<font color="Green">SUBSTITUTE(<font color="Purple">SUBSTITUTE(<font color="Teal">TRIM(<font color="#FF00FF">MID(<font color="Navy">SUBSTITUTE(<font color="Blue">SUBSTITUTE(<font color="Red">$A$2,CHOOSE(<font color="Green">COLUMNS(<font color="Purple">$A5:A5</font>),"""datetime","""headline","""source","""url"</font>),REPT(<font color="Green">" ",LEN(<font color="Purple">$A$2</font>)</font>),ROWS(<font color="Green">A$5:A5</font>)</font>),CHOOSE(<font color="Red">COLUMNS(<font color="Green">$A5:A5</font>),"""headline","""source","""url","""summary"</font>),REPT(<font color="Red">" ",LEN(<font color="Green">$A$2</font>)</font>),ROWS(<font color="Red">A$5:A5</font>)</font>),LEN(<font color="Blue">$A$2</font>),LEN(<font color="Blue">$A$2</font>)</font>)</font>),CHAR(<font color="#FF00FF">34</font>)&":",""</font>),CHAR(<font color="Teal">34</font>),""</font>),",",""</font>),"]",""</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

blafarm

Board Regular
Joined
Oct 14, 2018
Messages
59
Re: How to Extract Multiple Sections of Text in a String, Between Two Repeating Words

jtakw,

Absolutely excellent!

Thank you so much for your time and patience.

Cheers

(sending you a PM)


Edit: I sent you a PM but got this message: "jtakw has exceeded their stored private messages quota and cannot accept further messages until they clear some space."

So I'm posting it below:

-------------------------

Hi jtakw,

Wow !!!

You have been very kind, patient and generous with me.
I can't adequately tell you how much you have helped me.

However, I would like to make 'real world' expression of my appreciation.
Nothing crazy -- just a token of my gratitude.

Do you have a PayPal account?

If you do, please feel free to PM it to me.
 
Last edited:

blafarm

Board Regular
Joined
Oct 14, 2018
Messages
59
Re: How to Extract Multiple Sections of Text in a String, Between Two Repeating Words

Looks like my last post disappeared ...


Hi jtakw,

I'm sorry, but could you please help with a variation of the "Vertical" formula that you presented in your first post? (LINK HERE)

The "Vertical" formula you presented was for extracting the "headline" -- and it was as follows:

=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),""))

<tbody>
</tbody>

And I am now tying to modify that formula so that it extracts the "hasPaywall" values of true or false, again in a vertical succession of rows -- but I'm having trouble modifying it.

If possible, I would be very grateful to learn the proper formula for extracting the
"hasPaywall" values from both the Excel and Google version of that string.

Thanks very much.
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
4,714
Re: How to Extract Multiple Sections of Text in a String, Between Two Repeating Words

with PowerQuery aka Get&Transform:

datetimeheadlinesourceurlsummaryrelatedimagehasPaywall
1557328805000GE CEO says weaker quarters likely after 'good start' to yearReutershttps://cloud.iexapis.com/v1/news/article/1e7714c0-93f6-4921-af24-f3122a2e809eGeneral 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.GEhttps://cloud.iexapis.com/v1/news/image/1e7714c0-93f6-4921-af24-f3122a2e809e lang:enfalse
1557307800000GE Investors Gather to Elect Revamped Board Air GripesThe Wall Street Journalhttps://cloud.iexapis.com/v1/news/article/dfb8a33f-9194-493e-9b83-9709dc0d18a9General 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.GEhttps://cloud.iexapis.com/v1/news/image/dfb8a33f-9194-493e-9b83-9709dc0d18a9 lang:entrue
1557205509000IndiGo is making plans for another aggressive pushThe Economic Times Indiahttps://cloud.iexapis.com/v1/news/article/6ffe2450-5e9f-4166-b5f6-a26a79f10908by 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.GEhttps://cloud.iexapis.com/v1/news/image/6ffe2450-5e9f-4166-b5f6-a26a79f10908 lang:enfalse
1557085967000General Electric: Larry Culp Is The Man (For The Job)Seeking Alphahttps://cloud.iexapis.com/v1/news/article/87bdd7f9-1aec-4943-835f-7a436483cedeGeneral Electric reported strong Q1 2019 results that beat the consensus bottom-line estimate. The stock is up big since General Electric reported Q1 2019 resulGEhttps://cloud.iexapis.com/v1/news/image/87bdd7f9-1aec-4943-835f-7a436483cede lang:enfalse
1556912727000GE to build 500 MW coal power plant in KosovoSeeking Alphahttps://cloud.iexapis.com/v1/news/article/cf2c1bf4-2740-4f80-981b-7a68a8ec327fA 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 mGEhttps://cloud.iexapis.com/v1/news/image/cf2c1bf4-2740-4f80-981b-7a68a8ec327f lang:enfalse
1556901660000Good News! General Electric Burned Less Cash in Q1The Motley Foolhttps://cloud.iexapis.com/v1/news/article/492e3f89-1206-4547-a8a8-db086e773abbIs there light at the end of the tunnel for the troubled industrial conglomerate?GEhttps://cloud.iexapis.com/v1/news/image/492e3f89-1206-4547-a8a8-db086e773abb lang:enfalse
1556893560000The Zacks Analyst Blog Highlights: Apple Microsoft General Electric Ecolab and CelgeneZacks Investment Researchhttps://cloud.iexapis.com/v1/news/article/739fd73b-1e73-4d85-8e8b-7ced62a4e5eaThe Zacks Analyst Blog Highlights: Apple Microsoft General Electric Ecolab and CelgeneGEhttps://cloud.iexapis.com/v1/news/image/739fd73b-1e73-4d85-8e8b-7ced62a4e5ea lang:enfalse
1556889257000GE to build Kosovo's new 500 MW coal power plantReutershttps://cloud.iexapis.com/v1/news/article/f5e2699b-b92e-4aed-aa90-02b557ce5652Kosovo 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.GEhttps://cloud.iexapis.com/v1/news/image/f5e2699b-b92e-4aed-aa90-02b557ce5652 lang:enfalse
1556877840000Key Takeaways From General Electric's EarningsThe Motley Foolhttps://cloud.iexapis.com/v1/news/article/356ca854-67f0-4e1b-a782-4f1986da535fThe company delivered a solid but unspectacular quarter but that might be just what investors need right now.GEhttps://cloud.iexapis.com/v1/news/image/356ca854-67f0-4e1b-a782-4f1986da535f lang:enfalse
1556833186000General Electric: Still A MysterySeeking Alphahttps://cloud.iexapis.com/v1/news/article/2a0c3a5c-32fc-4d40-b8e7-458792672cdaGeneral 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 changeGEhttps://cloud.iexapis.com/v1/news/image/2a0c3a5c-32fc-4d40-b8e7-458792672cda lang:enfalse

Code:
[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Replace = Table.ReplaceValue(Table.ReplaceValue(Table.ReplaceValue(Table.ReplaceValue(Source,"""","",Replacer.ReplaceText,{"Column1"}),"}","",Replacer.ReplaceText,{"Column1"}),"{","",Replacer.ReplaceText,{"Column1"}),"]","",Replacer.ReplaceText,{"Column1"}),
    Split = Table.ExpandListColumn(Table.TransformColumns(Replace, {{"Column1", Splitter.SplitTextByAnyDelimiter({"datetime:","headline:","source:","url:","summary:","related:","image:","hasPaywall:"}, QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    RTR = Table.Skip(Split,1),
    IntDivide = Table.TransformColumns(Table.AddIndexColumn(RTR, "Index", 0, 1), {{"Index", each Number.IntegerDivide(_, 8), Int64.Type}}),
    Group = Table.Group(IntDivide, {"Index"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Custom", each Table.Column([Count],"Column1")),
    Extract = Table.TransformColumns(List, {"Custom", each Text.Combine(List.Transform(_, Text.From), "^"), type text}),
    Split1 = Table.SplitColumn(Extract, "Custom", Splitter.SplitTextByDelimiter("^", QuoteStyle.Csv)),
    RC = Table.RemoveColumns(Split1,{"Index", "Count"}),
    Trim = Table.TransformColumns(RC,{{"Custom.1", Text.Trim, type text}, {"Custom.2", Text.Trim, type text}, {"Custom.3", Text.Trim, type text}, {"Custom.4", Text.Trim, type text}, {"Custom.5", Text.Trim, type text}, {"Custom.6", Text.Trim, type text}, {"Custom.7", Text.Trim, type text}}),
    Ren = Table.RenameColumns(Trim,{{"Custom.1", "datetime"}, {"Custom.2", "headline"}, {"Custom.3", "source"}, {"Custom.4", "url"}, {"Custom.5", "summary"}, {"Custom.6", "related"}, {"Custom.7", "image"}, {"Custom.8", "hasPaywall"}})
in
    Ren[/SIZE]
you can remove unnecessary column(s)
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Re: How to Extract Multiple Sections of Text in a String, Between Two Repeating Words

Hi blafarm, my 19 year old dog just passed last night, I'll be busy today and/or tomorrow, need time to take care of things (and grief)…

In the meantime, can you clarify what exactly you need extracted?
Do you want every element extracted as sandy666 posted above?
Or, it sounds like you want something specific extracted Vertically, only hasPaywall, or along with something else, Vertically?
Showing a sample data with expected results would be helpful.

I'll try to work on it tonight...
 

blafarm

Board Regular
Joined
Oct 14, 2018
Messages
59
Re: How to Extract Multiple Sections of Text in a String, Between Two Repeating Words

Hi blafarm, my 19 year old dog just passed last night, I'll be busy today and/or tomorrow, need time to take care of things (and grief)…

In the meantime, can you clarify what exactly you need extracted?
Do you want every element extracted as sandy666 posted above?
Or, it sounds like you want something specific extracted Vertically, only hasPaywall, or along with something else, Vertically?
Showing a sample data with expected results would be helpful.

I'll try to work on it tonight...
jtakw,

Very sorry to hear of your loss. Sounds like a good friend you spent many years with. I've been there, and completely understand.

I only needed help with a formula to extract the "hasPaywall" values of "true" or "false" into vertical rows (like the vertical formula you provided for headlines in this link: (LINK HERE)

And, if it's not too much trouble, I was going to ask if you wouldn't mind helping with an Excel and Google version of that formula.

An example of the "expected results" I would like to achieve would look like this:


false
true
true
false
false
false

<tbody>
</tbody>


And the sample data is the same as before:
Excel and Google

Thanks so much, and again, my deepest condolences.
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Re: How to Extract Multiple Sections of Text in a String, Between Two Repeating Words

Let me know how these work for what you requested,

For your GOOGLE sheets version:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;">5767</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td 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}] </td></tr></tbody></table><p style="width:9.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet661 (5)</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A1</th><td style="text-align:left">=LEN(<font color="Blue">A2</font>)</td></tr></tbody></table></td></tr></table><br />

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">false</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">true</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">false</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">false</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">false</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">false</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">false</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">false</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">false</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">false</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";"></td></tr></tbody></table><p style="width:9.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet661 (5)</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A5</th><td style="text-align:left">=TRIM(<font color="Blue">SUBSTITUTE(<font color="Red">SUBSTITUTE(<font color="Green">SUBSTITUTE(<font color="Purple">SUBSTITUTE(<font color="Teal">MID(<font color="#FF00FF">SUBSTITUTE(<font color="Navy">SUBSTITUTE(<font color="Blue">A$2,"hasPaywall:",REPT(<font color="Red">" ",LEN(<font color="Green">$A$2</font>)</font>),ROWS(<font color="Red">A$5:A5</font>)</font>),"datetime",REPT(<font color="Blue">" ",LEN(<font color="Red">A$2</font>)</font>),ROWS(<font color="Blue">A$5:A5</font>)+1</font>),LEN(<font color="Navy">A$2</font>),LEN(<font color="Navy">A$2</font>)</font>),"{",""</font>),"}",""</font>),CHAR(<font color="Purple">34</font>),""</font>),"]",""</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,089,966
Messages
5,411,557
Members
403,379
Latest member
aarango

This Week's Hot Topics

Top