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
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
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}]
