However it only extracts the first headline and nothing after it.
If possible, I would like to extract all of headlines within the text in that cell, and generate a vertical array of those headlines so that it looks like this:
GE Posts Profit
GE Dividends Shrink
Is this possible?
Thanks very much.
The array formula that I entered in cell range B9:B11 is a user-defined function that I created. It extracts text from cell B3 based on a start and end string specified in cell C5 and C6 respectively.
Before using it you need to copy the VBA code below and paste it to a regular code module, instructions below.
Array formula in cell range B9:B11
To enter the array formula select cell range B9:B11. Type the formula and then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.
The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.
User Defined Function Syntax
ExtractText(text, start_word, end_word)
Required. A cell reference to the cell containing the text you want to extract.
Required. The first word you want to search for.
Required. The second word you want to search for. Text between the first and second word will be extracted, even if there are multiple instances.
Function ExtractText(text As String, start_word As String, end_word As String)
'Dimension variables and declare data types
Dim tmpArr() As Variant
ccount = UBound(Split(text, start_word)) - 1
'Iterate through text string
For i = 0 To ccount
'Find start position of instance
StartStr = InStr(text, start_word) + Len(start_word)
'Find end position of instance
EndStr = InStr(text, end_word)
'Extract first instance
tmpArr(i) = Mid(text, StartStr, EndStr - StartStr)
'Remove instance and save to variable again
text = Mid(text, EndStr + Len(EndStr), Len(text))
ExtractText = Application.Transpose(tmpArr)
Where do I put the code above?
Copy code above.
Go to tab "Developer", click the "Visual Basic" button to open VB Editor.
Click "Insert" on the menu.
Click "Module" to insert a module to your workbook.
Paste code to code module, see above image.
Exit VB Editor and return to Excel.
Note, make sure you save your workbook with file extension *.xlsm (macro-enabled) in order to keep the code.