Table of Contents
In Excel, you can extract an email address from a text string by using the FIND and LEFT/RIGHT functions. First, use the FIND function to locate the “@” symbol in the text string. Then, use the LEFT or RIGHT function to extract the characters before or after the “@” symbol. Finally, use the CONCATENATE function to combine the extracted characters with the “@” symbol and the remaining characters to create a complete email address. This method allows for easy and efficient extraction of email addresses from any text string in Excel.
Excel: Extract Email Address from Text String
You can use the following formula to extract an email address from a text string in Excel:
=IFERROR(TRIM(RIGHT(SUBSTITUTE(LEFT(A2,FIND(" ",A2&" ",FIND("@",A2))-1)," ",REPT(" ",LEN(A2))),LEN(A2))), "")
This particular formula extracts the email address from the text string in cell A2.
If no email address is found in cell A2, the formula simply returns a blank.
The following example shows how to use this formula in practice.
Example: How to Extract Email Address from Text String in Excel
Suppose we have the following column of text strings in Excel:
Suppose we would like to extract the email address from each text string.
To do so, we can type the following formula into cell B2:
=IFERROR(TRIM(RIGHT(SUBSTITUTE(LEFT(A2,FIND(" ",A2&" ",FIND("@",A2))-1)," ",REPT(" ",LEN(A2))),LEN(A2))), "")
We can then click and drag this formula down to each remaining cell in column B:
Column B now contains the email address from each text string in column A.
Note that cell A5 didn’t contain an email address so the formula simply returned a blank value.
Note: If a cell contains more than one email address, this formula will only return the first email address.