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.
- Select the cell B5, and write the formula in the cell
- =IF(ISNUMBER(SEARCH(“16×20″,A5)), “Baseball,BaseballPhotos16x20″, IF(ISNUMBER(SEARCH(“8×10″,A5)),”Baseball,Basebal lPhotos8x10″,””))
- Press Enter on the keyboard
- The function will return “Baseball,BaseballPhotos16x20”
- Paste the same formula in range B6
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 email@example.com