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.
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
'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)
'Decrease containers in array by 1
ReDim Preserve y(UBound(y) - 1)
FilterWords = Application.Transpose(y)
Where to do I copy the code?
Press Alt-F11 to open visual basic editor
Click Module on the Insert menu
Copy and paste the user defined function to module
Exit visual basic editor
Note, save your workbook with file extension *.xlsm to keep the user defined function in your workbook.