You can use the following formulas in Excel to return a value if a cell contains certain text:
Formula 1: Return Value if Cell Contains Exact Text
=IF(B2="Point Guard", B2, "")
This particular formula checks if the text in cell B2 is exactly equal to “Point Guard”. If it is, then the formula returns the text in cell B2. Otherwise, the formula returns a blank.
Formula 2: Return Value if Cell Contains Partial Text
=IF(COUNTIF(B2,"*Guard*"), B2, "")
This particular formula checks if cell B2 contains “Guard” anywhere in the cell. If it does, then the formula returns all of the text of cell B2. Otherwise, the formula returns a blank.
The following examples show how to use each formula in practice with the following dataset in Excel that contains information about various basketball players:
Example 1: Return Value if Cell Contains Exact Text
We can type the following formula into cell C2 to return the value in cell B2 if it is exactly equal to “Point Guard”:
=IF(B2="Point Guard", B2, "")
We can then click and drag this formula down to each remaining cell in column C:
If the text in column B is exactly equal to “Point Guard” then column C simply returns the text.
Otherwise, column C returns a blank.
Example 2: Return Value if Cell Contains Partial Text
We can type the following formula into cell C2 to return the value in cell B2 if it contains “Guard” anywhere in the cell:
=IF(COUNTIF(B2,"*Guard*"), B2, "")
We can then click and drag this formula down to each remaining cell in column C:
If the value in column B contains “Guard” anywhere in the cell, then column C returns all of the text from the cell.
Otherwise, column C returns a blank.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
Excel: Check if Cell Contains One of Several Values
Excel: Check if Cell Contains Date
Excel: Calculate Average If Cell Contains Text
