Excel Formulas to Extract the Word that Contains Specific Text ~ Easily!!

For instance, you have a list of text strings that are mixed with any numbers, special characters, and so on. Now you need to separate the word that contains certain text or symbol. How could you solve this problem quickly? Here we will show the formulas to extract the word that contains specific text in Excel. Let’s jump into this article!! Get an official version of ** MS Excel** from the following link: https://www.microsoft.com/en-in/microsoft-365/excel

Generic Formula:

If you want to extract the word that contains specific text in Excel, you can use the below formula.

*=TRIM(MID(SUBSTITUTE(A1,” “,REPT(” “,99)),MAX(1,FIND(“@”,SUBSTITUTE(A1,” “,REPT(” “,99)))-50),99)) *

Syntax Explanations:

TRIM – It helps to remove the extra spaces from the text and there is no space at the start or end of the text string. Read more on the TRIM function.

MID – This function helps to extract the number (starting from the left side) or characters from the given string. Read more on the MID function.

SUBSTITUTE – This function helps to replace existing text with new text in a text string when you want to replace text based on its content, not position. Read more on the SUBSTITUTE function.

REPT – In Excel, the REPT function is used to repeat characters a given number of times.

MAX – This function helps to return the largest numeric value from the range of input values. Read more on the MAX function.

FIND – The Excel, FIND function is used to find out the one text string inside the other.

*A1 * – It represents the input value.

Comma symbol (,) – It is a separator that helps to separate a list of values.

Parenthesis () – The main purpose of this symbol is to group the elements.

Minus Operator (-) – This symbol will help to subtract any two values.

Plus operator (+) – This symbol is used to add the values.

Practical Example:

Refer to the below example image.

First, we will enter the input text strings in Column B.

Now we have to extract the word that contains specific text from the input range.

Select any cell and type the above-given formula.

Finally, press the ** ENTER** key, you can get the result as shown below.

Bottom-Line:

Here we have discussed how to extract the word that contains specific text in Excel. Leave a comment or reply below to let me know what you think!

Thank you so much for visiting *Geek Excel!! **If you want to learn more helpful formulas, check out Excel Formulas *!! **

Read Also:

Formula to Find Position of Nth Occurrence of a Character in Text String!!

Excel Formulas to Count specific word in a range ~ Easy Tutorial!!

How to Count missing values in a list in Excel Office 365?

Excel Formulas to Extract the Month Number from Name!!