Exceljet

Quick, clean, and to the point

Extract word containing specific text

Excel formula: Extract word containing specific text
Generic formula 
=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),MAX(1,FIND("@",SUBSTITUTE(A1," ",REPT(" ",99)))-50),99))
Explanation 

To extract a word that contains specific text,you can use a formula based on several functions, including TRIM, LEFT, SUBSTITUTE, MID, MAX, and REPT. You can use this formula to extract things like email addresses, or other substrings with a unique id.

In the example shown, the formula in C5 is:

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

How this formula works

The gist: this formula "floods" the space between words in a text string with a large number of spaces, finds and extracts the substring of interest, and uses the TRIM function to clean up the mess.

Working from the inside out, the original text in B5 is flooded with spaces using SUBSTITUTE:

SUBSTITUTE(B5," ",REPT(" ",99))

This replaces each single space with 99 spaces.

Note: 99 is just an arbitrary number that represents the longest word you need to extract. 

Next, the FIND function locates the specific character (in this case, "@") inside the flooded text:

FIND("@",SUBSTITUTE(B5," ",REPT(" ",99)))-50

FIND returns the position of the "@" in this text, from which 50 is subtracted. Subtracting 50 effectively "walks back" the position to someplace in the middle of the spaces preceding the substring of interest. In the example shown, the calculated position is 366.

The MAX function is used to handle the problem of the substring appearing first in the text. In that case, the position will be negative, and MAX is used to reset to 1.

With a starting positing established , MID is used to extract 99 characters of text, starting at 366 from the text in B5, again flooded with space:

MID(SUBSTITUTE(B5," ",REPT(" ",99)),366,99)

This extracts the substring of interest, with a lot of space characters before and after.

Finally, the TRIM function is used to trim leading and trailing space, and returns substring containing the special character.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.