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.

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

Featured Posts

Leave a Reply

Your email address will not be published. Required fields are marked *