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

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.