Forum Discussion
Mitch Clark
Jun 13, 2018Copper Contributor
Excel function or macro to isolate email addresses in a list
I have a long list of contacts that follow this example: Contact Name Title Phone Address Primary Practice Email Address Company Name City The information is al...
Matt Mickle
Jun 13, 2018Bronze Contributor
Try using this code in the below scenario (see attached file for reference):
Sub GetWords()
Dim LRow As Integer
Dim LngLp As Integer
Dim TwoLp As Integer
Dim fndText As Integer
Dim Sht As Worksheet
On Error Resume Next 'Suppress Errors... for when we don't find a match
TwoLp = 2
'Define worksheet that has data on it....
Set Sht = Sheets("Sheet1")
'Get last row for words based on column A
LRow = Sht.Cells(Rows.Count, "A").End(xlUp).Row
'Loop through lists and find matches....
For LngLp = 2 To LRow
'Look for word...
fndText = Application.WorksheetFunction.Search("@", Sht.Cells(LngLp, "A"))
'If we found the word....then
If fndText > 0 Then
Sht.Cells(TwoLp, "B") = Sht.Cells(LngLp, "A")
fndText = 0 'Reset Variable for next loop
TwoLp = TwoLp + 1
End If
Next LngLp
End Sub
You will need to add the code to the workbook. The forum won't let me post macro enabled files due to security risks...