If you want a dynamic Excel worksheet where you want your output cell to showcase a certain value based on a specific text in another cell, you have some work to do as Excel doesn’t have any built-in feature or simple formula for that. However, don’t worry, we got this cover for you. Today, we will show and explain 7 different methods to assign a value if a cell contains a specific word in Excel. We hope you will stay with us till the end of the article.

## 📁 Download Excel File

Download this for better understanding and practice yourself.

## Learn to Assign Value if a Cell Contains Word in Excel with These 7 Methods

Unfortunately, Excel doesn’t have any easy way to automatically assign a value if a cell contains a word. Thus, we will have to create our own formula for this purpose. In this regard, we will use a couple of functions like **IF**, **ISTEXT**, **ISNUMBER**, **FIND**, **SEARCH**, **ISERROR**, **IFERROR**, **COUNTIF**, and **VLOOKUP**. As we progress we will explain how these functions work and how our formula will show our desired output. Furthermore, we will attempt to create a VBA code to do the same. Let’s take a look at how we can create formulas using these functions.

**Method 1**

### 1. Applying IF Function

The first method we are going to explore is using the **IF function**. **IF** is a logical function in Excel. It returns one result if a condition is **TRUE** and another one if it is **FALSE**. Let’s break down the syntax of the **IF **function.

Syntax:

**IF(logical_test, value_if_true, [value_if_false])**

Here,

**Logical_test** – the condition you want to look into.

**value_if_true** – The value that should be returned if the logical test returns **TRUE**.

**value_if_false** – The value that should be provided if the logical test returns **FALSE**.

Now we will use the **IF** function to assign value to our text. If the target cell includes any specific word or number, the formula below will produce a specified value. For this, we will use different types of Genres of books. Now I have collected only Mythological books. So whichever Rows contain ‘Mythology’, I want to assign the word “Collected” in adjacent Rows. Let’s write a formula for that.

**⬇️⬇️ STEPS ⬇️⬇️ **

- First, Choose the output cell, and in the
**Formula Bar**, use the following formula. For instance, we selected**C6**as our output cell.

**=IF(B6=”Mythology”,”Collected”,”Not Collected”)**

- Following that, hit
**Enter**and you will see the word “Collected” appear in cell**C6**as**B6**contains Mythology.

- To conclude, double-click the
**Fill Handle**to**copy the formula**to the rest of the Rows. As you can clearly see those without “Mythology” showing “Not Collected” just like I wanted.

**🔨 Formula Breakdown**

👉 Now, what have we done here? In our **logical_test** section, we provided **B6=”Mythology”** meaning our **IF** function will look for the word “Mythology” in the **B6** cell. Then we asked the formula to print “Collected” if it finds “Mythology” in the **value_if_true** section. Finally, we provided “Not collected” if the **IF** function doesn’t find “Mythology” in the selected cells.

** Read More: ****10 Ways to Select Multiple Cells Without Mouse in Excel**

**Method 2**

### 2. Using IF with ISTEXT Function

If your Rows are mixed with text and numbers and you only want to assign a specific value to your text data, you can do that by nesting **ISTEXT** in your **IF function**. Remember **ISTEXT **only recognizes cells with texts but it doesn’t distinguish between different types of texts. Now, we have a range of cells where we have mixed data. We want to check which cells contain text and put values with respect to them. Let’s write our formula.

**⬇️⬇️ STEPS ⬇️⬇️ **

- Firstly, select the output cell and write this formula in the
**Formula Bar**. We selected**C6**.

**=IF(ISTEXT(B6),”Collected”,”Not Found”)**

- Next, hit
**Enter**and the formula will return “Collected” into the output cell since the**B6**cell contains text rather than a number or date.

- Finally, use
**Fill Handle**to drag the formula to the rest of the Rows. Notice the Rows containing numbers. Since there is no text, the formula resorted to “Not Found” in each of the numbers Rows.

**🔨 Formula Breakdown**

👉 In the formula,**ISTEXT(B6) **searches the data of cell **B6 **whether it is a text or not. When it finds text it will return **TRUE **for the **IF** function. But in the case of other datatypes like numbers, it will return **FALSE**.

👉 At the end, the **IF** function takes the **TRUE **given by **ISTEXT** and returns ‘“Collected” just as we wrote in the formula. If you notice **B2**, you will see there is no text so **ISTEXT** provided **FALSE **to **IF**, and in turn, **IF** showed “Not Found”.

** Read More: ****How to Select Multiple Cells in Excel (10 Quick Methods)**

**Method 3**

### 3. Combination of IF, ISNUMBER & FIND Functions

Now we will create a formula for a more complicated setting. If you have cells with several texts and you want to match just one of them and return the value, you will be able to use this formula. As usual, we will use the **IF** function and we will nest two new functions, **ISNUMBER** and **FIND** to assign values to our matching text. Let’s find out about the functions.

Syntax of **ISNUMBER** functions:

**ISNUMBER(value)**

The function determines whether or not a cell contains a number. If the value is a number, it will return **TRUE**; otherwise, it will return **FALSE**, meaning it only recognizes numbers.

**FIND(find_text, within_text, [start_num])**

The arguments are as follows:

**find_text** – The text you’re looking for. It must be used.

**within_text** – The text you’re looking for in your Find_text.It is also required.

**start_num** – Specifies the character at which the search should begin. Character 1 is the first character within the text. If start_num is not specified, it is considered to be 1. It is optional.

**FIND** searches one text string within a second text string and return the number of the first text string’s initial position from the first character of the second text string.

Now that we have familiarized ourselves with the functions let’s create a formula to assign value to a cell that contains word in Excel. For illustration, we have a dataset where we need to figure out the Genre if there is a certain word in the name of the books. We have two types of books. We’ll assign the Genre “Mythology” and “History” if the same words can be found within the name of the books.

**⬇️⬇️ STEPS ⬇️⬇️ **

- First, like always, we’ll select our destination cell. In our case, it is cell
**C6**. - Now, we shall write the following formula in the
**Formula Bar**.

**=IF(ISNUMBER(FIND(“Mythology”,B6)),”Mythology”,”History”)**

- In the end, press Enter to see the result. The word “Mythology” will be printed on
**C6**.

- Finally, double-click on the
**Fill Handle**to cover the entire column.

**🔨 Formula Breakdown**

Let’s realize what we have accomplished here.

👉 In the Formula, **FIND(“Mythology”,B6) **will search for the word “Mythology” in cell **B6** and return its position within the Name of the book. Here, **FIND** will return 11 as it starts in the 11th position from the first word. If the function doesn’t find the word it will show an Error message.

👉 Next,** ISNUMBER** will take that number and return **TRUE **for the** IF** Function. If there is no number or error, **ISNUMBER** will simply show **FALSE**.

👉 And finally, as we instructed **IF** will print “Mythology” if it gets **TRUE **and “History” if it gets **FALSE**.

** Read More: ****14 Examples to Select Cells with Keyboard Shortcut in Excel**

**Method 4**

### 4. Combination of IF, ISERROR & SEARCH Functions

Similarly, we can assign value to a cell that contains a word by nesting the **ISERROR** and **SEARCH** functions into the **IF **function. First, we shall get acquainted with the functions.

**ISERROR(value)**

Excel’s **ISERROR **function returns **TRUE **for any error type, including** #N/A**, **#VALUE**!, **#REF**!, **#DIV/0!**,** #NUM!**, **#NAME?**, and **#NULL!** If there is no error, it prints **FALSE**. Moreover, you can print any custom message by using the **IF **function just like we did here.

**SEARCH(find_text,within_text,[start_num])**

The arguments are as follows:

**find_text** – The text you’re looking for. It must be used.

**within_text **– The text you’re looking for in your Find_text.It is also required.

**start_num** – Specifies the character at which the search should begin. Character 1 is the first character within the text. If start_num is not specified, it is considered to be 1. It is optional.

The **SEARCH** function finds one text string within a second text string and returns the number of the first text string’s commencing position from the very first character of the second text string. This is very similar to the **FIND** function except **FIND** is case-sensitive while **SEARCH** is not. We will use the previous example here as well.

**⬇️⬇️ STEPS ⬇️⬇️**

- We’ll start by selecting our destination cell
**C6**. - Now enter the following formula into the
**Formula Bar**.

**=IF(ISERROR(SEARCH(“mythology”,B6)),”History”,”Mythology”)**

- Afterward, press
**Enter**and we’ll see our desired result.

- Finally, drag
**Fill Handle**to copy the formula to the rest of the Rows.

**🔨 Formula Breakdown**

Let’s break the formula down:

👉 In the formula, **SEARCH(“mythology”,B6) **will try to find the word “mythology” irrespective of its case in cell **B6**. Like the **FIND **function, it will return 11.

👉 Unlike** ISNUMBER**, the **ISERROR **function will search for errors from the **SEARCH **function. As instructed, if it finds an error it will return **TRUE **for the **IF **function. Here, **ISERROR** found a number rather than an error so it returned FALSE.

👉 Finally, **IF** printed “Mythology” when it got **FALSE **from the **ISERROR **function. In some cases, you can see it printed “History” because **ISERROR **returned **TRUE **when **SEARCH **didn’t find “mythology”.

**Method 5**

### 5. Using IF with COUNTIF Function

Another way we can assign value to a cell that contains the specific word in Excel is by merging **IF** and **COUNTIF** functions. But before we do that we need to get introduced to **Wildcard**. So what is **Wildcard** in Excel? A** Wildcard** is a type of character that can replace any other character. Meaning, if you don’t know a particular character, you can substitute a **Wildcard**. Excel recognizes two popular **Wildcard** characters: an asterisk (*) and a question mark (?). Since we will use an asterisk(*) in this example, I’ll explain in depth how it works.

The most generic **Wildcard** character is the asterisk (*), which may represent any number of characters. As an example:

**ch*** – matches any word beginning with “ch,” such as Cheesecake, channel, chop, chemistry, and so on.

***ch** – replaces any text string that ends in “ch,” such as cornstarch, batch, bunch, and so on.

***ch*** – any word that has “ch” in any place, such as kitchen, teacher, machine, archive, and so on.

Furthermore,** Wildcards** are useful in any case where a partial match is required. They can be used as comparison parameters for **filtering data**, locating items with a similar component, or conducting imprecise matching in formulas.

Now the syntax of the **COUNTIF** function:

**COUNTIF(range, criteria)**

The arguments are:

**range** – The group of cells to be counted. The range can be made up of numbers, arrays, a specified range, or pointers to numbers. Values that are blank or text are ignored.

**criteria** – A number, expression, cell reference, or text string indicating which cells should be counted.

Both arguments are a must-use. Now let’s use them in our formula. We have a new dataset of books where we will assign the plot set by recognizing specific text from the Names of the books. Like if the word “Egypt” comes in any form in the book’s name, our formula will show that the book is based on “Egypt”.

**⬇️⬇️ STEPS ⬇️⬇️ **

- Selecting our destination cell
**C6**, we put the following formula in the**Formula Bar**.

**=IF(COUNTIF(B6,”*Egypt*”),”Egypt”,”Norse”)**

- Hit
**Enter**for the result of our formula.

- And drag the
**Fill Handle**to do the same for every row.

**🔨 Formula Breakdown**

👉 By using **COUNTIF(B6,”*Egypt*”)**, we asked Excel to find the number of times the word “Egypt” or any variation of it comes in the Name of the book in cell **B6**. Consequently, it will give 0. As you notice we have used a wildcard *Egypt* to declare any word that has Egypt in it.

👉 Finally, the value for the **IF** function is 0 which means **FALSE**, the second parameter, which is the intended output, is returned. In the later Rows, you get to see the first parameter as a result since **COUNTIF** provides 1 for **IF**, meaning **TRUE**.

**Method 6**

### 6. Nesting Vlookup into IF and IFERROR Functions

We already used a lot of functions in the previous scenarios. However, we have two more functions to show you. They also serve the purpose to assign value to a cell that contains word in Excel. **VLOOKUP** and **IFERROR** functions. When you need to find anything in a table or a range by row, use **VLOOKUP** and the **IFERROR** function can be used to detect and manage faults in a calculation. If a formula evaluates to an error, **IFERROR** returns the value you specify; otherwise, it returns the formula’s result. Let’s dive a little deeper to know the functions’ syntaxes.

Syntax the formulas:

**VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])**

The arguments:

**lookup_value** – The value for which you want the function to look. This should be in the first column of the range of cells in the table array parameter.

**table_array** – The cell range where **VLOOKUP **will look for the lookup value and return value. The lookup value must be in the first column of the table. The cell range must also include the desired return value.

**col_index_num** – The **column index number** (beginning with 1 for the table array’s first column where data started) containing the return value.

**range_lookup** – A boolean value that indicates whether VLOOKUP should seek for an approximate or precise match. The approximate match is 1 or **TRUE**, whereas the exact match is 0 or **FALSE**.

The first three arguments are mandatory.

**IFERROR(value, value_if_error)**

The arguments are as follows:

v**alue** – this argument checks whether there is an error or not.

**value_if_error** – If the formula produces an error, this value is returned.** #N/A**,** #VALUE!**, **#REF!**, **#DIV/0!**, **#NUM!**, **#NAME?**, or **#NULL!** are the error types that are evaluated.

We will use the first example here as well. Let’s see how our formula works.

**⬇️⬇️ STEPS ⬇️⬇️ **

- Firstly, select the output cell
**C6**. - Next, put the following formula in the
**Formula Bar**.

**=IFERROR(IF(VLOOKUP($B$6,$B6,1,TRUE)=”Mythology”,”Collected”,”Not Collected”),”Not Found”)**

- In a similar fashion, hit
**Enter**and see the magic.

- Lastly, there is nothing for you to do except drag the
**Fill Handle**to copy the formula.

**🔨 Formula Breakdown**

👉 Firstly,**VLOOKUP($B$6,$B6,1,TRUE) **searches the criteria “Mythology” which is in **$B$6**.We put a dollar(**$**) sign in between the column and the row number because we want our criteria to be fixed. In the **table_array** segment, we provided **$B6**.Notice here only the column remains fixed because as we drag down the formula the Row number will change. We provided column index number 1 as column **B** is our first data column and finally, we wanted an approximate match so as range_lookup we gave **TRUE**. When the function finds the given word, it will return **TRUE **otherwise **FALSE**.

👉 Secondly, the **IF** function will look for **TRUE/FALSE** from** the VLOOKUP function** and For **TRUE **it will return “Collected” and for **FALSE**, it will return “Not Collected”.

👉 Finally, **IFERROR** looks for an error. If there isn’t one it will print the result given by the **IF** function. But when **IF** returns an error, it will print “Not Found” just like it did in Cell **C9** and **C16**.

**Method 7**

### 7. With VBA Code

Finally, the only option left for us to assign a value if a cell contains word in Excel is to use the VBA script. Before we started writing VBA code, we enabled the **Developer** tab on our Ribbon. Click on the link to see the process of **accessing the Developer tab**.

We used the example we discussed in Method 5 where we’ll assign Plot settings based on a specific word in Books names. Let’s write our code and get the result.

**⬇️⬇️ STEPS ⬇️⬇️ **

- First, click on the
**Developer**tab and select**Visual Basic**.

- Next, a dialogue box named
**Microsoft Visual Basic for Applications**will show up. Here, click**Insert**and then**Module**.

- Now, copy and paste the following code into the white module.

```
Sub assigning_values()
For Each x In Range("B6:B12")
If InStr(1, x.Value, "Egypt") > 0 Then
x.Offset(0, 1) = "Egypt"
Else
x.Offset(0, 1) = "Norse"
End If
Next
End Sub
```

- Finally, apply the code by pressing
**F5**. You will achieve the same result as Method 5.

## 📄 Important Notes

`🖊️`

**FIND** is a case-sensitive function while **SEARCH **is not. Use **SEARCH **if the case of the text is the least of concern.

`🖊️`

**ISTEXT **only recognizes text while **ISNUMBER **recognizes numbers.

`🖊️`

Only value_if_error is supported by **IFERROR**. If there is no mistake, it always returns the result of the value/formula that was assessed.

`🖊️`

**ISERROR **gives you additional freedom by allowing you to handle both scenarios – what should happen if there is an error and what should happen if there is no error.

## 📝 Takeaways from This Article

`📌`

Readers will be able to understand each of the functions easily.

`📌`

From now on, readers will be able to nest different functions.

`📌`

Readers will become more comfortable with using Excel.

## Conclusion

Finally, we arrive at the end of this article. We have learned a lot about Excel’s functions and I hope I detailed the procedures well enough for you to understand them. My efforts will be successful if you can comprehend them nicely. If you have any queries, please leave a comment. I’ll try my best to respond as soon as possible. Moreover, if you know of any other interesting approaches to assign value to a cell that contains a word that I haven’t included, please leave a comment. Have fun using Excel, and you can find more tutorials at **Excelden.com**. Thanks.

## Related Articles

**3 Ways to Divide a Group of Cells by a Number in Excel****7 Ways to Check If Multiple Cells Are Equal in Excel****3 Ways to Clear Contents Without Deleting Formulas in Excel**