dunndealpr
New member
- Joined
- Jun 6, 2013
- Messages
- 5
- Reaction score
- 0
- Points
- 0
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
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