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
