Filter words containing a given string in a cell range [UDF]
The image above demonstrates a User Defined Function that extracts all words containing a given string that you can specify. In this case it is a @ sign. A User Defined Function is a custom function that you can build yourself in the visual basic editor.
Example, cell range B1:M50 contains random sentences, I have inserted some random emails in this range, see image above.
Array formula in cell range B3:B7:
To enter an array formula, type the formula in a cell 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.
VBA code
'Name function Function FilterWords(rng As Range, str As String) As Variant() 'Declare variables Dim x As Variant, Wrds() As Variant, Cells_row As Long Dim Cells_col As Long, Words As Long, y() As Variant 'Redimension variable ReDim y(0) 'Save values in range to array variable Wrds = rng.Value 'Iterate through array variable For Cells_row = LBound(Wrds, 1) To UBound(Wrds, 1) For Cells_col = LBound(Wrds, 2) To UBound(Wrds, 2) 'Extract words in cell to an array x = Split(Wrds(Cells_row, Cells_col)) 'Iterate through word array For Words = LBound(x) To UBound(x) 'Check if value in array is equal to the given string If InStr(x(Words), str) Then 'Save value to another array y(UBound(y)) = x(Words) 'Increase containers in array by 1 ReDim Preserve y(UBound(y) + 1) End If Next Words Next Cells_col Next Cells_row 'Decrease containers in array by 1 ReDim Preserve y(UBound(y) - 1) 'Return array FilterWords = Application.Transpose(y) End Function
Where to do I copy the code?
- Press Alt-F11 to open visual basic editor
- Press with left mouse button on Module on the Insert menu
- Copy and paste the user defined function to module
- Exit visual basic editor
Filter emails category
Question: How to extract email addresses from this sheet? Answer: It depends on how the emails are populated in your worksheet? […]
User defined function category
This user defined function creates a unique distinct list of words and how many times they occur in the selected […]
This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]
This article demonstrates a user defined function that lists files in a ggiven folder and subfolders. A user defined function is […]
The image above demonstrates a user-defined function in cell range B6:D7 that allows you to search a folder and subfolders […]
Question: How do I divide values equally into groups (3 lists or less)? This post shows you two different approaches, […]
This blog post describes how to create permutations, repetition is NOT allowed. Permutations are items arranged in a given order meaning […]
This article demonstrates how to convert a range of cells containing strings separated by a delimiter into a range of […]
This blog post describes how to create a list of unique distinct words from a cell range. Unique distinct words […]
This article demonstrates a User Defined Function (UDF) that counts unique distinct cell values based on a given cell color. […]
The SUBSTITUTE and REPLACE functions can only handle one string, the following User-Defined Function (UDF) allows you to substitute multiple […]
This article demonstrates formulas that count values in cell based on a delimiting character. The image above shows a formula […]
AJ Serrano asks: I have a column where each rows contains different values and I wanted to obtain the duplicate […]
This article explains how to perform multiple lookups based on values in one cell with a delimiting character using a […]
This article describes a User Defined Function that lookups values in multiple cross reference tables based on two conditions. A […]
Question:I would like to reduce the number of open items by identifying positive and negative amounts that net to zero […]
This article demonstrates formulas and a UDF that searches for values in a table based on concatenated values and returns […]
Macros and custom functions are great, they can automate many tedious tasks. To have them available whenever you need them, […]
This blog post demonstrates a custom function (UDF) that creates permutations. Repetition is allowed. The custom function lets you specify the […]
In this vba tutorial I am going to show you how to return values from an udf, depending on where […]
This article describes how to find a sum from a range of numbers using a user defined function. Let´s see […]
Functions in this article
More than 1300 Excel formulas
Excel formula categories
Excel categories
2 Responses to “Filter words containing a given string in a cell range [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 notice that your UDF requires spaces to delimit the email address. For example, if the email address is surrounded by parentheses or adjacent to punctuation marks, those will remain attached to the returned email addresses. This is because you used a general word parser as the basis for your UDF. I had a robust email address parser function that I wrote awhile ago, so I wrote a front-end function (the UDF) that repeatedly calls it as needed; doing this makes the UDF return only the email addresses no matter what other delimiting characters surround them. Just copy the following two functions into a standard Module, then select a column of cells (more than you think email addresses exist), enter this formula in the Formula Bar (change the range as necessary)...
=FindEmailAddresses(B1:M50)
and then press CTRL+SHIFT+ENTER to commit the array formula. By the way, the GetEmailAddress function below can be used as a stand-alone function by itself... it returns a single email address (the first it finds in the text passed to it). Okay, here are the functions...
Function FindEmailAddresses(Rng As Range) As Variant()
Dim Temp As String, Cell As Range, EM() As Variant
ReDim EM(0)
For Each Cell In Rng
Temp = Cell.Value
Do While InStr(Temp, "@")
EM(UBound(EM)) = GetEmailAddress(Temp)
Temp = Replace(Temp, "@", "", 1, 1)
ReDim Preserve EM(UBound(EM) + 1)
Loop
Next
ReDim Preserve EM(UBound(EM) - 1)
FindEmailAddresses = WorksheetFunction.Transpose(EM)
End Function
Function GetEmailAddress(ByVal S As String) As String
Dim X As Long, AtSign As Long
Dim Locale As String, DomainPart As String
Locale = "[A-Za-z0-9.!#$%&'*/=?^_`{|}~+-]"
Domain = "[A-Za-z0-9._-]"
AtSign = InStr(S, "@")
For X = AtSign To 1 Step -1
If Not Mid(" " & S, X, 1) Like Locale Then
S = Mid(S, X)
If Left(S, 1) = "." Then S = Mid(S, 2)
Exit For
End If
Next
AtSign = InStr(S, "@")
For X = AtSign + 1 To Len(S) + 1
If Not Mid(S & " ", X, 1) Like Domain Then
S = Left(S, X - 1)
If Right(S, 1) = "." Then S = Left(S, Len(S) - 1)
GetEmailAddress = S
Exit For
End If
Next
End Function
Rick Rothstein (MVP - Excel),
Thank you for your valuable comment! I tried your functions and they work as you described!