Article updated on January 20, 2018

This udf extracts all words containing a specified string.


Cell range B1:M50 contains random sentences. I have inserted some random emails in this range.

VBA code

Function FilterWords(rng As Range, str As String) As Variant()
Dim x As Variant, Wrds() As Variant, Cells_row As Long
Dim Cells_col As Long, Words As Long, y() As Variant

ReDim y(0)
Wrds = rng.Value

For Cells_row = LBound(Wrds, 1) To UBound(Wrds, 1)
  For Cells_col = LBound(Wrds, 2) To UBound(Wrds, 2)
    x = Split(Wrds(Cells_row, Cells_col))
      For Words = LBound(x) To UBound(x)
        If InStr(x(Words), str) Then
          y(UBound(y)) = x(Words)
          ReDim Preserve y(UBound(y) + 1)
        End If
      Next Words
  Next Cells_col
Next Cells_row

ReDim Preserve y(UBound(y) - 1)
FilterWords = Application.Transpose(y)
End Function

Where to do I copy the code?

  1. Press Alt-F11 to open visual basic editor
  2. Click Module on the Insert menu
  3. Copy and paste the user defined function into module
  4. Exit visual basic editor

How to use user defined function in excel

  1. Select sheet1
  2. Select cell A1
  3. Type FilterWords(B1:M50, "@") in formula bar
  4. Press Ctrl + SHIFT + ENTER
  5. Select cell A1:A5
  6. Press Ctrl + SHIFT + ENTER

Download excel sample file for this tutorial.

Filter emails from a cell range.xls
(Excel 97-2003 Workbook *.xls)