And all email addresses have been extracted from the cell text. And then press Enter key, select the cell B1, and drag the fill handle over to the range that you need the formula. Then save the code and enter the formula =ExtractEmailFun(A1) in an adjacent blank cell, see screenshot:Ĥ. GetStr = getStr & p = Index1 + 1 To Len(extractStr)ģ. If Mid(extractStr, p, 1) Like CheckStr Then Index1 = VBA.InStr(Index, extractStr, = '' Function ExtractEmailFun(extractStr As String) As String Click Insert > Module, and paste the following macro in the Module window. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.Ģ. Download and free trial Now!Įxcept the above formula, a User Defined Function also can help you get the email address from the text string.ġ. Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. Kutools for Excel’s Extract Email Address can help you to extract the email addresses from the text strings quickly and conveniently. If there are more than one email address in a cell, the formula will only extract the first address.Įxtract Multiple Email Addresses from text strings If the cells don’t contain the email addresses, the formula will display error values.ģ. The punctuation after the email address also will be extracted.Ģ. And the email addresses in the range have been extracted from the text string. Then press Enter key, then select the cell B1, and drag the fill handle to the range that you want to contain this formula. In the adjacent cell B1, enter this formula =TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND (' ',A1&' ', REPT(' ',LEN(A1))),LEN(A1))).Ģ. Here I introduce you a long formula to extract only the email addresses from the text in Excel. How could you quickly only get the email addresses from the cell text?Įxtract email address from text string with FormulaĮxtract email address from text string with User Defined FunctionĮxtract email address from text string with VBA codeĮxtract email address from text string with Kutools for Excel When you import some email addresses form Website to Excel worksheet, there always contains irrelevant text, but now you just want to extract the pure email addresses from the text string (see following screenshots). How to quickly extract email address from text string?

Angle text in excel file