How to classify fruit into different categories (Orange, Strawberry, Durian)

Kay: What’s the formula to detect the word “orange” and returns a number?

Jason: =IF(IISNUMBER(SEARCH(“orange”,A1),1,””)

A! contains the description. SEARCH worksheet function returns a value if the word is found. If not, it returns an #VALUE! Error. You need to use ISNUMBER to return no results. IF worksheet function is to return the number 1 if found and blank “” if not found

You need to use this if you have hundreds and thousands of rows and you want the answer fast.

Kay: What if there is more than one word, either orange or strawberry?

Jason: =IF(IISNUMBER(SEARCH(“orange”,A1),1,””) + IF(IISNUMBER(SEARCH(“strawberry”,A1),1,””)

Jason: You can use two of the same formula and add them together. If you get strawberry or orange, get get 1. If you get both, you get 2.

Kay: 1 is a category and 2 is another category.

Jason: Then you can use


Kay: What if I have many words for the orange category, blood, California, Sunkist, Jaffar, etc? And another set of words for the strawberry category? And a third set for durian?

Jason: The formula will become very long. Need to change to this.


This problem can be solved with a long formula or use Power Query to automate it. Learn how to do it in Our Courses.

AI Chatbot Avatar
⚠️ Welcome to our chatbot! Please note that this chatbot is designed to provide general information about our Excel and Power BI courses, and should not be relied upon as legal or financial advice. Any personal information collected by the chatbot will be handled in accordance with our privacy policy. By interacting with the chatbot, you agree to our terms and conditions.