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.
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.
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:
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 email@example.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.