UDF – Extracting Email Address from Text

 

In case you want a process to help you in retrieving the email address from the string then this article is for you. In this article, we will create UDF to pull the email id from text.

Question): The data I have contains too much information in text format. I want a VBA code to help me to extract as many email ids from the text to minimize my manual efforts.

We need to follow the below steps:

  • Click on Developer tab
  • From Code group, select Visual Basic

img1

 

Enter the following code in the standard module:

Function ExtractEmailFromText(s As String) As String    Dim AtTheRateSignSymbol As Long    Dim i As Long

    Dim TempStr As String

    Const CharList As String = “[A-Za-z0-9._-]“

 

    AtTheRateSignSymbol = InStr(s, “@”)

    If AtTheRateSignSymbol = 0 Then

        ExtractEmailFromText = “”

    Else

        TempStr = “”

        For i = AtTheRateSignSymbol – 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

        TempStr = TempStr & “@”

        For i = AtTheRateSignSymbol + 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

    If Right(TempStr, 1) = “.” Then TempStr = _

       Left(TempStr, Len(TempStr) – 1)

    ExtractEmailFromText = TempStr

End Function

img2

 

  • In cell B2, the formula is
  • =ExtractEmailFromText(A2)

We will get the result. Refer below snapshot:

 

img3

 

The above code will extract first email address in case there is more than 1 email id in a cell.

In this way, we can retrieve email is from text.

image 19

Download – Extracting Email Address From Text – xlsm



Example:


Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>