Hassan Shuvo
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.
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:
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
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)
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:
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.
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
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.
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.
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”.
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:
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.
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:
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.
The arguments are as follows:
value – 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.
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
