Table of Contents
To assign a value in Excel if a cell contains a certain word, you can use the IF function. This function takes the format IF(logical_test, value_if_true, value_if_false) and tests if the cell contains the word. If it does contain the word, the value_if_true will be returned, otherwise the value_if_false will be returned. You can then use this value for further calculations.
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")
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.
Cite this article
stats writer (2026). How to Automatically Assign Values in Excel Based on Cell Content. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-to-assign-a-value-in-excel-if-cell-contains-a-word/
stats writer. "How to Automatically Assign Values in Excel Based on Cell Content." PSYCHOLOGICAL SCALES, 3 Jan. 2026, https://scales.arabpsychology.com/stats/how-to-assign-a-value-in-excel-if-cell-contains-a-word/.
stats writer. "How to Automatically Assign Values in Excel Based on Cell Content." PSYCHOLOGICAL SCALES, 2026. https://scales.arabpsychology.com/stats/how-to-assign-a-value-in-excel-if-cell-contains-a-word/.
stats writer (2026) 'How to Automatically Assign Values in Excel Based on Cell Content', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-to-assign-a-value-in-excel-if-cell-contains-a-word/.
[1] stats writer, "How to Automatically Assign Values in Excel Based on Cell Content," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, January, 2026.
stats writer. How to Automatically Assign Values in Excel Based on Cell Content. PSYCHOLOGICAL SCALES. 2026;vol(issue):pages.