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:-
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.
For more example, click here
If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
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
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
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?
Please help me with this my formula not working
="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
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)?
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
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.
This Formula =IF(ISNUMBER(SEARCH(“16×20?,A2)), “Baseball,BaseballPhotos16x20?, IF(ISNUMBER(SEARCH(“8×10?,A2)),”Baseball,Basebal lPhotos8x10?,””))
Returns False If Value Not Found,
If We Want To Place A Different Value in Place of False, What Should i Do??
Hi Steve Smith,
Can you send us your Excel file or mention here your question in Excel format.
Thanks
I have address fields that contain the street address, city and ZIP code and, of course, the strings are of different lengths; I need to be able to either parse strings of variable lengths, or search each field for a particular city (i.e. West Palm Beach) and enter a result in another cell. The first option would be the most ideal - is there a way to achieve this goal? Thank you.