Excel formula to extract email addresses

Exceljet has a formula posted here, which is effective at extracting email addresses, and other strings, from text excerpts.

=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",99)),MAX(1,FIND("@",SUBSTITUTE(A2," ",REPT(" ",99)))-50),99))

The formula works by pulling any word which includes the character that is a subject of the FIND formula, which in this case is the '@' symbol. It works by adding spaces to the target cells - so this part of the formula:

SUBSTITUTE(A2," ",REPT(" ",99))

. . . adds 99 spaces around each word:

We SUBSTITUTE blank spaces in A2 with a space 99 times using the REPT formula. (The REPT formula just repeats whatever string you enter for a set number of times. I.e., =REPT("cat",10) gives: catcatcatcatcatcatcatcatcatcat). The number of spaces that are added sets a limit on the length of the string that can be pulled. This solution will not pull a word more than 100 characters in length.

The FIND formula then finds where in the cell the "@" or searched for term appears after the spaces have been added.

=FIND("@",SUBSTITUTE(A2," ",REPT(" ",99)))

The MID formula then extracts the text with the searched for term and the spaces around it:

=MID(SUBSTITUTE(A2," ",REPT(" ",99)),MAX(1,FIND("@",SUBSTITUTE(A2," ",REPT(" ",99)))-50),99)

The TRIM formula then simply removes the extra spaces.

We can extract full words which contain the searched for string because the complete formula surrounds them with buffer blank spaces. They are then easy to target - once the position of one character or segment is located, it's easy to extract the full word and then cut off the extra spaces.

The formula will also successfully locate words, or parts of words and turn the full word in which the segment appears.

A #VALUE error will be given if the searched for text does not appear in the target cell.