Assume that, you have a list of text strings in one column, and you need to categorize them based on certain keywords. How could you finish this complicated job quickly and easily in Excel? Not to worry. In this article, I introduce some formulas which can quickly categorize the text based on given keywords in Excel. Let’s get into this article!! Get an official version of ** MS Excel** from the following link: https://www.microsoft.com/en-in/microsoft-365/excel
Generic Formula:
- The below formula will help you to categorize the text string.
=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,text)),0))
Syntax Explanations:
- INDEX – This function will help to return the value at a given position in a range or array. Read more on the INDEX function.
- MATCH – It helps to locate the position of a lookup value in a row, column, or table. Read more on the MATCH function.
- TRUE – The TRUE function will help to return the value TRUE if the given conditions will be TRUE or Vice Versa.
- ISNUMBER – The ISNUMBER function returns TRUE when a cell contains a number, and FALSE if there will not be a number.
- SEARCH – The Excel** SEARCH function** locates the character between two text strings and returns to the number of the starting position of the first text string from the first character of the second text string.
- Categories – It represents the criteria value for the input values.
- Text – It represents the input table value.
- Comma symbol (,) – It is a separator that helps to separate a list of values.
- Parenthesis () – The main purpose of this symbol is to group the elements.
Practical Example:
Let’s consider the below example image.
- First, we will enter the input values in Column B , and now we are going to categorize the text string based on the keywords which are given in Column E and Column F.
- So, apply the given formula to the formula bar section.
- After that, Press CTRL + SHIFT + ENTER Keys.
- Finally, we will get the result in the selected cell.
Wrap-Up:
From this tutorial, you can get some clarification on how to categorize the text string based on keywords in Excel. Hope you like it. Let me know if you have any doubts regarding this article or any other article on this site. To learn more, check out Geek Excel *and Excel Formulas *!!
Top comments (0)