Extract Email Address from string.

Some time we feel difficult to Email address Extract from string .Because it is time waste at first copy than paste other place . But it is easy way extract it by visual basic code writing for  create a function than it is use 
 for Extract  Email address.Save time and enjoy.This simple code insert bellow copy and pest in open workbook (tools->Macro->Visual basic editor->Insert module->paste )easily find solution.

Function Email(s As String) As String
    Dim AtSignLocation As Long
    Dim i As Long
    Dim TempStr As String
    Const CharList As String = "[A-Za-z0-9._-]"
    
    'Get location of the @
    AtSignLocation = InStr(s, "@")
    If AtSignLocation = 0 Then
        Email = "" 'not found
    Else
        TempStr = ""
        'Get 1st half of email address
        For i = AtSignLocation - 1 To 1 Step -1
            If Mid(s, i, 1) Like CharList Then
                TempStr = Mid(s, i, 1) & TempStr
            Else
                Exit For
            End If
        Next i
        If TempStr = "" Then Exit Function
        'get 2nd half
        TempStr = TempStr & "@"
        For i = AtSignLocation + 1 To Len(s)
            If Mid(s, i, 1) Like CharList Then
                TempStr = TempStr & Mid(s, i, 1)
            Else
                Exit For
            End If
        Next i
    End If
    'Remove trailing period if it exists
    If Right(TempStr, 1) = "." Then TempStr = _
       Left(TempStr, Len(TempStr) - 1)
    Email = TempStr
End Function
'-------------Md .Shaiful Islam Talukder------------------ .

Download the workbook

Tweet