Forum Discussion

Mitch Clark's avatar
Mitch Clark
Copper Contributor
Jun 13, 2018

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

I want to isolate just the email address.

Excel Desktop
Macros & VBA

1 Reply

  • Matt Mickle's avatar
    Matt Mickle
    Bronze Contributor
    Jun 13, 2018

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