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

May 19, 2021 - by Bill Jelen

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.

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:

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:

Formula Solution

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

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

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 up with:

Figure 125 shows the result of the formula.

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.