Results 1 to 2 of 2

Thread: How can I activate this formula?

  1. #1

    How can I activate this formula?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hey all. Using Excel 2013 on Windows 7.

    A nice person gave me the following Excel formula last year to extract email addresses from data in column G into column E.
    I have to use this formula again but being a bit of an Excel noob, I've completely forgotten how to do it. Can someone please explain this to me like I'm 6? You'd basically be saving my life

    Thanks much. Much.



    Function GetEmailAddress(Sin As String) As String
    Dim X As Long, AtSign As Long, AtSign2 As Long, StartAt As Long, S As String, subS As String
    Dim Locale As String, Domain As String
    Locale = "[A-Za-z0-9.!#$%&'*/=?^_`{|}~+-]"
    Domain = "[A-Za-z0-9._-]"
    StartAt = 1
    Do
    S = Mid(Sin, StartAt)
    AtSign = InStr(StartAt, S, "@")
    If AtSign < 2 Then Exit Do
    If Mid(S, AtSign - 1, 1) Like Locale Then
    For X = AtSign To 1 Step -1
    If Not Mid(" " & S, X, 1) Like Locale Then
    subS = Mid(S, X)
    If Left(subS, 1) = "." Then subS = Mid(subS, 2)
    Exit For
    End If
    Next
    AtSign2 = InStr(subS, "@")
    For X = AtSign2 + 1 To Len(subS) + 1
    If Not Mid(subS & " ", X, 1) Like Domain Then
    subS = Left(subS, X - 1)
    If Right(subS, 1) = "." Then subS = Left(subS, Len(subS) - 1)
    GetEmailAddress = GetEmailAddress & ", " & subS
    Exit For
    End If
    Next
    End If
    StartAt = AtSign + 1
    Loop
    GetEmailAddress = Mid(GetEmailAddress, 3)
    End Function

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,429
    Articles
    0
    Excel Version
    Excel 2016
    Hold the ALT key, press the F11 key.

    Go to Insert then select Module.

    Paste the code in the VB Editor (the big white pane on the right).

    Close the whole window, and in the sheet enter your formula like:

    =GetEmailAddress(A1)

    where A1 contains the original string.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •