Excel function or macro to isolate email addresses in a list

[Mitch Clark]

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 all in the same column.

I want to isolate just the email address.

[#2]

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...