How can I assign a value in Excel based on whether a cell contains a specific word?

By /

To assign a value in Excel based on whether a cell contains a specific word, you can use the IF function. This function allows you to check the content of a cell and perform different actions based on the result. By using the IF function, you can assign a specific value or perform a specific calculation if the cell contains the desired word, and a different value or calculation if it does not. This can be useful for organizing and analyzing data in Excel, allowing you to quickly identify and categorize information based on specific criteria.

Excel: If Cell Contains Word then Assign Value

You can use the following formula in Excel to assign a value to a cell if it contains a particular word:

=IF(COUNTIF(B2,"*"&$B$14&"*"), B2, "No")

In this example, if cell B2 contains the word in cell B14, then the value in cell B2 is returned.

Otherwise, a value of “No” is returned instead.

The following example shows how to use this formula in practice.

Example: If Cell Contains Word then Assign Value in Excel

Suppose we have the following dataset in Excel that shows the position of various basketball players:

Suppose we would like to return the name of the position in column C if the corresponding cell in column B contains the word “Starting” (as specified in cell B14) or “No” otherwise.

We can type the following formula into cell C2 to do so:

=IF(COUNTIF(B2,"*"&$B$14&"*"), B2, "No")

We can then click and drag this formula down to each remaining cell in column C:

Excel if cell contains word then assign value

Notice that column C returns the name of the position in column B if the position contains “Starting” somewhere in the name or it returns “No” otherwise.

How This Formula Works

Recall the formula that we used to assign a value to a cell if it contained a particular word:

=IF(COUNTIF(B2,"*"&$B$14&"*"), B2, "No")

The COUNTIF(B2, “*”&$B$14&”*”) formula checks if cell B2 contains the string “Starting” anywhere in the cell and returns 1 if it does or 0 otherwise.

We then use an IF function to return the value in cell B2 if the COUNTIF function returns 1.

Otherwise, the IF function returns “No” if the COUNTIF function returns 0.

Additional Resources

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

x

Related terms:

  1. how to assign a value in excel if cell contains a word
  2. Can SPSS select cases based on whether a string contains a specific text?
  3. Excel: Shade a Cell Based on Another Cell Value
  4. Excel Formula: Replace If Cell Contains Specific Text
  5. How to apply conditional formatting to a cell based on the value in another cell?
  6. How can I change the cell color in Excel based on a specific date?
  7. How do I assign a number value to text in Excel?
  8. Excel: If Cell Contains Text then Return Value
  9. How to check if cell in Excel contains date value?
  10. How can I display a default value in Excel based on another cell?