How can I use Excel to return a specific value if a cell contains certain text?

By /

Excel is a powerful tool that can be used to retrieve specific values based on certain criteria. In order to return a specific value if a cell contains certain text, you can use the IF function in Excel. This function allows you to set conditions and specify what action should be taken if those conditions are met. By using the IF function and combining it with the SEARCH function, you can check if a cell contains a specific text and return a specified value if the condition is met. This can be useful in various situations, such as data analysis or creating customized reports. With Excel’s flexibility and functionality, it is a valuable tool for efficiently retrieving specific values based on text criteria.

Excel: If Cell Contains Text then Return Value

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.

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

x

Related terms:

  1. Excel: If Cell Contains Text then Return Value
  2. How can I create an Excel formula that returns the value of another cell if a certain cell is blank?
  3. Excel Formula: Replace If Cell Contains Specific Text
  4. How can I assign a value in Excel based on whether a cell contains a specific word?
  5. How do I construct an Excel formula to return a specific value if a cell is between two numbers?
  6. Excel: Apply Conditional Formatting if Cell Contains Partial Text
  7. What is Excel: Apply Conditional Formatting if Cell Contains Text
  8. How do I check if cell contains a partial text in Excel?
  9. How do I check if a cell contains text from a list in Excel?
  10. How do I select cases in SPSS if a string contains specific text?