# If a Cell Contains a Certain Text Then Put a Specified Number in another Cell

In this article we will learn how to specified number, if a cell contains certain text. We will use the combination of IF, ISNUMBER and SEARCH functions in Microsoft Excel.

Let’s understand with a simple exercise how we can specify the text in a cell based on the text in another cell. We have 2 numbers in the range A2:A4. Now we want to specify the text in range B2:B3.

To specify the text based on number follow the below mentioned steps:-

• Select the cell B5, and write the formula in the cell
• =IF(ISNUMBER(SEARCH("16x20",A5)), "Baseball,BaseballPhotos16x20", IF(ISNUMBER(SEARCH("8x10",A5)),"Baseball,Basebal lPhotos8x10",""))
• Press Enter on the keyboard
• The function will return “Baseball,BaseballPhotos16x20”
• Paste the same formula in range B6

Formula Explanation:-

SEARCH function will search the number in cell, if the searched number will be in cell then function will return numeric value else #VALUE! Error.

On the basis of Search function’s result ISNUMBER function will give the result in TRUE or FALSE.

IF function will check the logical test is true then result is set, in condition of FALSE will check for another value.

This is the method you can follow if you want to put specified text with a number in one cell based on the data in another cell.

We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com

1. I have what seems to be a easy problem but giving me a headache, so on my spreadsheet I have two sheets let’s say SheetA and SheetB, what I want is on SheetA there are columns populated with yes no options when an option is chosen i want a specific cells in SheetB to auto populate a date. How would I effectively do this?

="IF(ISNUMBER(SEARCH(*EM-9260*,Y27)),*EM-9260*,pc)"
EM-9260-15 single
EM-9219-3-8 pair
EM-9263-4 Pair
EM-9263-6 Single
I want
be the output has paired no need for single

3. My customer has a program where they pick what items they need to order and then they send it to me to requesting pricing for the list of items. The description of the items are always the same. But they only send what items they need for that week.

The list is always in one column with 1 item per row.

Is there a way that I can search a column and have the formula give the price in the next column / same row?

Ex: If in column c they ask for 2" conduit (let's say it happens to be in row 24 today). How can I get it search the column and automatically put the price of \$2.12 in the next column ( column d, row 24)?

4. I need help with a similar formula,

So what I have is loads of data, I then need to rate each one in a class between 1 - 9. Each class has a value range.
Example:
85
72
80
64
88

Class 1 = 96

5. What if I have more than 150 such cases where I have to use IF statement ? Excel is not allowing m eto add more than 64 if statements in a single formula.

6. This Formula =IF(ISNUMBER(SEARCH(“16×20?,A2)), “Baseball,BaseballPhotos16x20?, IF(ISNUMBER(SEARCH(“8×10?,A2)),”Baseball,Basebal lPhotos8x10?,””))