5 Excel Contains Text Formulas

Introduction to Excel Contains Text Formulas

Excel is a powerful spreadsheet software that offers a wide range of formulas and functions to manipulate and analyze data. One of the commonly used formulas in Excel is the “Contains Text” formula, which allows users to search for specific text within a cell or a range of cells. In this article, we will explore the different types of “Contains Text” formulas in Excel, their syntax, and examples of how to use them.

Using the IF Function with Contains Text

The IF function in Excel is used to test a condition and return one value if the condition is true and another value if the condition is false. The “Contains Text” formula can be used with the IF function to search for specific text within a cell. The syntax of the IF function with “Contains Text” is:
=IF(ISNUMBER(SEARCH(“text”,A1)),“Yes”,“No”)
Where “text” is the text you want to search for, and A1 is the cell that contains the text.

Using the SEARCH Function with Contains Text

The SEARCH function in Excel is used to search for a specific text within a cell or a range of cells. The syntax of the SEARCH function is:
=SEARCH(“text”,A1)
Where “text” is the text you want to search for, and A1 is the cell that contains the text. The SEARCH function returns the position of the first character of the text if it is found, and a #VALUE! error if it is not found.

Using the FIND Function with Contains Text

The FIND function in Excel is similar to the SEARCH function, but it is case-sensitive. The syntax of the FIND function is:
=FIND(“text”,A1)
Where “text” is the text you want to search for, and A1 is the cell that contains the text. The FIND function returns the position of the first character of the text if it is found, and a #VALUE! error if it is not found.

Using the COUNTIF Function with Contains Text

The COUNTIF function in Excel is used to count the number of cells that meet a specific condition. The “Contains Text” formula can be used with the COUNTIF function to count the number of cells that contain specific text. The syntax of the COUNTIF function with “Contains Text” is:
=COUNTIF(A1:A10,”text”)
Where “text” is the text you want to search for, and A1:A10 is the range of cells that contains the text.

Using the FILTER Function with Contains Text

The FILTER function in Excel is used to filter a range of cells based on a specific condition. The “Contains Text” formula can be used with the FILTER function to filter a range of cells that contain specific text. The syntax of the FILTER function with “Contains Text” is:
=FILTER(A1:A10,ISNUMBER(SEARCH(“text”,A1:A10)))
Where “text” is the text you want to search for, and A1:A10 is the range of cells that contains the text.

📝 Note: The above formulas can be used in different scenarios to search for specific text within a cell or a range of cells in Excel.

Here is a table that summarizes the different types of “Contains Text” formulas in Excel:

Formula	Syntax	Description
IF Function	=IF(ISNUMBER(SEARCH(“text”,A1)),“Yes”,“No”)	Tests a condition and returns one value if the condition is true and another value if the condition is false
SEARCH Function	=SEARCH(“text”,A1)	Searches for a specific text within a cell or a range of cells
FIND Function	=FIND(“text”,A1)	Searches for a specific text within a cell or a range of cells (case-sensitive)
COUNTIF Function	=COUNTIF(A1:A10,”text”)	Counts the number of cells that meet a specific condition
FILTER Function	=FILTER(A1:A10,ISNUMBER(SEARCH(“text”,A1:A10)))	Filters a range of cells based on a specific condition

In summary, the “Contains Text” formulas in Excel are powerful tools that can be used to search for specific text within a cell or a range of cells. The different types of formulas, including the IF function, SEARCH function, FIND function, COUNTIF function, and FILTER function, can be used in different scenarios to achieve the desired results.