Extract text between words [UDF]
I have a somewhat related question, if you don't mind:
I have very large amount of text in a single cell, and I would like to extract multiple instances of text that appear between two specific words.
For example, here is the sample text in one cell:
{"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"}
This following formula does a good enough job of extracting the first headline:
=MID(C2,SEARCH("headline",C2)+2,SEARCH("source:",C2)-SEARCH("headline",C2)-4)
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
GE Bankrupt
Is this possible?
Thanks very much.
Update! Excel 365 users check this article: Extract values between two given delimiting strings
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)
Arguments
text | Required. A cell reference to the cell containing the text you want to extract. |
start_word | Required. The first word you want to search for. |
end_word | 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. |
VBA code
Function ExtractText(text As String, start_word As String, end_word As String) 'Dimension variables and declare data types Dim tmpArr() As Variant 'Count instances ccount = UBound(Split(text, start_word)) - 1 ReDim tmpArr(ccount) '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)) Next i ExtractText = Application.Transpose(tmpArr) End Function
Where do I put the code above?
- Copy code above.
- Go to tab "Developer", press with left mouse button on the "Visual Basic" button to open VB Editor.
- Press with left mouse button on "Insert" on the menu.
- Press with left mouse button on "Module" to insert a module to your workbook.
- Â Paste code to code module, see above image.
- Exit VB Editor and return to Excel.
User defined function category
This article demonstrates how to convert a range of cells containing strings separated by a delimiter into a range of […]
This article demonstrates formulas that count values in cell based on a delimiting character. The image above shows a formula […]
This article demonstrates a User Defined Function (UDF) that counts unique distinct cell values based on a given cell color. […]
This article describes how to count unique distinct values in list. What is a unique distinct list? Merge all duplicates to one […]
This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]
AJ Serrano asks: I have a column where each rows contains different values and I wanted to obtain the duplicate […]
This article demonstrates a user defined function that extracts duplicate values and also count duplicates. Example, the image below shows a list containing […]
This post describes a custom function (User defined Function) that extract values existing only in one out of two cell […]
The User Defined Function demonstrated above extracts unique distinct records also considering upper and lower case letters. For example, a record […]
The User Defined Function demonstrated in the above picture extracts unique distinct values also considering lower and upper case letters. […]
This blog post describes how to create a list of unique distinct words from a cell range. Unique distinct words […]
This blog post describes how to create a list of unique words from a cell range. Unique words are all […]
I tried the array formula in this post: Filter common values between two ranges using array formula in excel to […]
The image above demonstrates a User Defined Function that extracts all words containing a given string that you can specify. In […]
This article describes how to find a sum from a range of numbers using a user defined function. Let´s see […]
Question:I would like to reduce the number of open items by identifying positive and negative amounts that net to zero […]
In this post I will describe a basic user defined function with better search functionality than the array formula in […]
This user defined function creates a unique distinct list of words and how many times they occur in the selected […]
Macros and custom functions are great, they can automate many tedious tasks. To have them available whenever you need them, […]
In this vba tutorial I am going to show you how to return values from an udf, depending on where […]
This article demonstrates a user defined function that lists files in a ggiven folder and subfolders. A user defined function is […]
This blog post describes how to create permutations, repetition is NOT allowed. Permutations are items arranged in a given order meaning […]
This blog post demonstrates a custom function (UDF) that creates permutations. Repetition is allowed. The custom function lets you specify the […]
This article explains how to perform multiple lookups based on values in one cell with a delimiting character using a […]
Sean asks: Sheet1A B C D 8 Country Europe 9 Lights 100 10 Type A 200 11 12 Country USA […]
The image above demonstrates a user-defined function in cell range B6:D7 that allows you to search a folder and subfolders […]
This article demonstrates formulas and a UDF that searches for values in a table based on concatenated values and returns […]
Question: How do I divide values equally into groups (3 lists or less)? This post shows you two different approaches, […]
The SUBSTITUTE and REPLACE functions can only handle one string, this article demonstrates two ways to handle more than one […]
This post describes a User Defined Function that searches multiple ranges and adds corresponding values across worksheets in a workbook. A […]
This article describes a User Defined Function that lookups values in multiple cross reference tables based on two conditions. A […]
Functions in this article
More than 1300 Excel formulas
Excel categories
3 Responses to “Extract text between words [UDF]”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
I get an error when I use this code. I followed everything to a T...
"Invalid Name Error"
Bob,
which vba row was highlighted?
Nice job, indeed. Thank you for sharing.