Microsoft Office Tutorials and References
In Depth Information
EXTRACT AN E-MAIL ADDRESS FROM A CELL CONTAINING OTHER TEXT
While the following formula would take some time to build, it is clever and
remarkably simple in its operation:
=TRIM(MID(SUBSTITUTE(" "&A1," ",REPT(" ",20)),FIND("@",SUBST
ITUTE(" "&A1," ",REPT(" ",20)))-20,40))
The formula initially uses the SUBSTITUTE function to replace every occurrence
of a space with 20 spaces. This serves to separate every word in the text by
Original Text: Tell email@example.com hello
New Text: Tell firstname.lastname@example.org
The FIND function locates the @ sign in the new text. The MID function starts 20
characters before the @ and grabs text for 40 characters. I used 40 characters
because that should be long enough to handle any possible e-mail address. In
fact, it would fail if you had john.jacob.jingleheimer.schmidt@gmail.
com in your database. However, for a normal-sized e-mail address, you end
up with something like:
Finally, the TRIM function removes all leading and trailing spaces, so you end
Figure 125 shows the result of the formula.
Figure 125. h
e formula isolates the e-mail portion of the text.
Summary: You can use three different methods to extract the e-mail address
from a cell that contains an e-mail address as well as other text