Microsoft Office Tutorials and References
In Depth Information
EXTRACT AN E-MAIL ADDRESS FROM A CELL CONTAINING OTHER TEXT
EXTRACT AN E-MAIL ADDRESS
FROM A CELL CONTAINING OTHER TEXT
Challenge: You have cells that contain e-mail addresses as well as other text.
You need to extract just the e-mail address from a cell.
Solution: There are three solutions to this problem: Use a macro, use a user-
deﬁ ned function, or use a very large formula.
The macro solution makes use of the SPLIT function in VBA. Let’s say that a
cell contains the text Write to firstname.lastname@example.org to book a seminar .
If you pass this text to the SPLIT function and indicate that the text should be
split at every space character, VBA will return an array, with each word at a new
position of the array. Figure 124 shows the value of x after you use SPLIT .
Figure 124. h e SPLIT function returns a 0-based array,
with each word broken out.