Microsoft Office Tutorials and References
In Depth Information
EXTRACT AN E-MAIL ADDRESS FROM A CELL CONTAINING OTHER TEXT
The macro then simply loops through each position in the x array, looking for
a word that matches the pattern *@*.*. (Rather than test for equality, the LIKE
operator looks for a word that matches a pattern.)
When a match is found, the macro writes the e-mail address to the right of the
original cell.
With the following macro, you select all the cells that contain text e-mail
addresses somewhere within the cell:
Sub getEmailMacro()
Dim x As Variant
Dim i As Integer
For Each cell In Selection
x = Split(cell.Value, " ")
For i = 0 To UBound(x)
If x(i) Like "*@*.*" Then
cell.Offset(0, 1) = x(i)
Exit For
End If
Next i
Next cell
End Sub
When you run the macro, the e-mail address portion of the cell is written to the
right of the original values.
User-Defi ned Function Solution
You can adapt the macro above into a user-defi ned function that accepts one
cell value as an argument and returns the e-mail portion of the text:
Public Function getEmail(note As String) As String
Dim x As Variant
Dim i As Integer
x = Split(note, " ")
For i = 0 To UBound(x)
If x(i) Like "*@*.*" Then
getEmail = x(i)
Exit Function
End If
Next i
End Function
Part
3
 
Search JabSto ::




Custom Search