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:

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")

Here is how this formula works:

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:

How to Count Duplicates in Excel

How to Count Frequency of Text in Excel

How to Calculate Average If Cell Contains Text in Excel