Extract An E-mail Address From A Cell Containing Other Text


May 19, 2021 - by Bill Jelen

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-defined function, or use a very large formula.

Macro Solution

The macro solution makes use of the SPLIT function in VBA. Let’s say that a cell contains the text Write to lora@mrexcel.com 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. The SPLIT function returns a 0-based array, with each word broken out.
Figure 124. The SPLIT function returns a 0-based array, with each word broken out.

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:

e9781615474011_i0227.jpg

When you run the macro, the e-mail address portion of the cell is written to the right of the original values.

User-Defined Function Solution

You can adapt the macro above into a user-defined function that accepts one cell value as an argument and returns the e-mail portion of the text:

e9781615474011_i0228.jpg

Formula Solution

While the following formula would take some time to build, it is clever and remarkably simple in its operation:

e9781615474011_i0229.jpg

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 many spaces:

 

Original Text: Tell bill@mrexcel.com hello

New Text: Tell bill@mrexcel.com
hello

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:

e9781615474011_i0231.jpg

Finally, the TRIM function removes all leading and trailing spaces, so you end up with:

e9781615474011_i0232.jpg

Figure 125 shows the result of the formula.

Figure 125. The formula isolates the e-mail portion of the text.
Figure 125. The 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

 

Source: Extract from String on the MrExcel Message Board.

Title Photo: Solen Feyissa on Unsplash


This article is an excerpt from Excel Gurus Gone Wild.



Bill Jelen is the author / co-author of:
MrExcel 2020 - Seeing Excel Clearly

This is a 4th edition of MrExcel LX. Updates for 2020 include: Ask a question about your data, XLOOKUP, Power Query's Data Profiling tools, How Geography Data Types decide which Madison, A SEQUENCE example for descending 52 weeks, Exchange Rates support in Stock Data Types, How to collapse the Search box, How to leave effective feedback for Microsoft, How to post your worksheet to the MrExcel Board using XL2BB.