Table of Contents
Excel is an incredibly powerful and versatile tool that can be used to do a wide variety of tasks, from simple calculations to complex data analysis. One of the most useful features in Excel is the ability to use formulas to quickly and easily return values based on certain conditions. For example, the formula “If cell contains text then return value” can be used to quickly identify and return specific values from a series of cells. This can be extremely useful when analyzing data or creating reports. By using this formula, users can quickly identify and return particular values from a series of cells, which can make data analysis and reporting much more efficient.
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
=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.