How to Extract Data with Specific Text in Excel. This article explores the process of extracting data based on specific text criteria in Excel. Text operations play a crucial role when dealing with string values, such as extracting first names, middle names, or last names from a full name. Excel treats string operations differently from numerical values, necessitating distinct approaches.
How to Extract Data with Specific Text in Excel
To locate a specific substring within a string, we can utilize the combination of the ISNUMBER and FIND functions.
ISNUMBER Function:
- Checks if a cell value contains a number.
- Syntax:
=ISNUMBER(value)
FIND Function:
- Returns the position of the first occurrence of a specific character or text string.
- Syntax:
=FIND(find_text, within_text, [start_num])
Example:
Consider two columns: Column A contains the given string, and Column B contains the substring. 
To check if the substring is present in the given string, use the formula in cell C2:
=ISNUMBER(FIND(B2, A2))
Explanation:
- FIND extracts the substring from cell B2 and searches for it in the given string (A2).
- ISNUMBER checks if the substring is present, returning True or False accordingly.
Copy the formula to other cells using the shortcut Ctrl+D to apply the formula across the selected range.
Alternate Formulas:
- Case-Insensitive Search:
=ISNUMBER(SEARCH("specific text", "source text"))
- Case-Sensitive Search:
=ISNUMBER(FIND("specific text", "source text"))
Example:
Suppose you want to check if any cell contains the substring “Excel” (case-insensitive) in a list of strings. Use the formula in cell D2 and drag it down:
=ISNUMBER(SEARCH(C2, B2))
This formula returns TRUE if the substring is found, else FALSE.
Explanation:
- SEARCH and FIND return the location of the found substring or an error if not found.
- ISNUMBER checks if the result is a number (substring found), returning True or False accordingly.
Customize the output using the IF statement:
=IF(ISNUMBER(SEARCH("specific text", "source text")), "value if true", "value if false")
This article provides insights into extracting data with specific text criteria in Excel, facilitating various applications of string operations. For more in-depth Excel formulas and calculations, explore additional articles in our collection.
