Easy Excel Formula to Categorize Text with Keywords

Are you looking for an effective way to categorize text data in Excel based on specific keywords? Excel provides powerful formulas that allow you to quickly and easily assign categories to your text data by searching for the presence of certain keywords.

Whether you’re working with customer feedback, survey responses, or any other type of text data, being able to automatically categorize your data can save you a significant amount of time and effort. In this comprehensive guide, we will walk you through the step-by-step process of using Excel formulas to categorize your text data efficiently.

Why Should You Categorize Text with Keywords in Excel?

When working with large datasets in Excel, it’s common to encounter text data that needs to be categorized based on specific criteria. For example, you might have a list of customer feedback comments that you want to categorize into different sentiment groups (positive, negative, neutral) based on the presence of certain keywords. Manually categorizing each comment can be time-consuming and prone to errors, especially when dealing with a large volume of data.

Another scenario where categorizing text data becomes crucial is when analyzing survey responses. Suppose you have an open-ended question in your survey where respondents can provide free-form text answers. By categorizing these responses based on keywords, you can quickly identify common themes, sentiments, or issues mentioned by the respondents.

Excel Formula: SEARCH and ISNUMBER

To categorize text data in Excel using keywords, we can leverage the power of the SEARCH and ISNUMBER functions. Here’s how they work:

• SEARCH: The SEARCH function searches for a specific substring within a given text string and returns the position of the first occurrence of the substring. If the substring is not found, it returns a #VALUE! error. The syntax for the SEARCH function is as follows:

  SEARCH(substring, text, [start_position])

• substring: The substring you want to search for within the text.
• text: The text string in which you want to search for the substring.
• [start_position] (optional): The position within the text string from where you want to start the search. If omitted, the search starts from the beginning of the text.
• ISNUMBER: The ISNUMBER function checks whether a value is a number and returns TRUE if it is, and FALSE otherwise. It is particularly useful for handling the #VALUE! error returned by the SEARCH function. The syntax for the ISNUMBER function is straightforward:

  ISNUMBER(value)

• value: The value you want to check if it is a number.

By combining these two functions, we can create a formula that searches for specific keywords within a text string and returns a corresponding category based on the presence of those keywords.

Step-by-Step Guide to Categorize Text with Keywords in Excel

Let’s walk through an example to illustrate how to use the Excel formula to categorize text data based on keywords.

Step 1: Prepare Your Data

Start by organizing your data in an Excel worksheet. In this example, let’s assume you have a column called “Comments” in column A that contains the text data you want to categorize. Ensure that your data is properly formatted and free of any inconsistencies or errors.

Step 2: Define Keywords and Categories

Create a separate table in your worksheet to define the keywords and their corresponding categories. This table will serve as a reference for your categorization formula. For example:

You can add more keywords and categories based on your specific requirements. Make sure to choose keywords that are relevant and representative of the categories you want to assign.

Step 3: Construct the Formula

In an empty column next to your “Comments” column, enter the following formula:

=IF(ISNUMBER(SEARCH("great",A2)),"Positive",IF(ISNUMBER(SEARCH("excellent",A2)),"Positive",IF(ISNUMBER(SEARCH("good",A2)),"Positive",IF(ISNUMBER(SEARCH("bad",A2)),"Negative",IF(ISNUMBER(SEARCH("poor",A2)),"Negative",IF(ISNUMBER(SEARCH("terrible",A2)),"Negative","Neutral"))))))

Let’s break down the formula:

• The formula uses nested IF statements to check for the presence of each keyword in the text data.
• It starts by searching for the keyword “great” using SEARCH("great",A2). If the keyword is found, ISNUMBER returns TRUE, and the formula assigns the category “Positive” to that cell.
• If the keyword “great” is not found, the formula moves on to the next keyword, “excellent”, and performs the same check.
• This process continues for each keyword until a match is found or all keywords have been checked.
• If none of the specified keywords are found, the formula assigns the category “Neutral”.

The formula essentially checks each keyword in the order you specify and assigns the corresponding category based on the first match it finds. If no match is found, it defaults to the “Neutral” category.

Step 4: Apply the Formula to the Dataset

Once you have entered the formula in the first cell of the category column, you can drag the formula down to apply it to the entire column of text data. Excel will automatically update the cell references in the formula for each row.

After applying the formula, you will see the assigned categories appear next to each text entry based on the presence of the specified keywords.

Advanced Techniques to Categorize Text with Keywords in Excel

Case-Insensitive Search

By default, the SEARCH function is case-sensitive, meaning it distinguishes between uppercase and lowercase letters. If you want to perform a case-insensitive search, you can modify the formula slightly:

=IF(ISNUMBER(SEARCH(LOWER("great"),LOWER(A2))),"Positive",IF(ISNUMBER(SEARCH(LOWER("excellent"),LOWER(A2))),"Positive",...

In this modified formula, both the keyword and the text data are converted to lowercase using the LOWER function before performing the search. This ensures that the search is case-insensitive and will match keywords regardless of their capitalization.

Handling Multiple Keywords per Category

If you have multiple keywords associated with each category, you can modify the formula to check for all the keywords within each IF statement. For example:

=IF(ISNUMBER(SEARCH("great",A2))+ISNUMBER(SEARCH("excellent",A2))+ISNUMBER(SEARCH("good",A2))>0,"Positive",IF(ISNUMBER(SEARCH("bad",A2))+ISNUMBER(SEARCH("poor",A2))+ISNUMBER(SEARCH("terrible",A2))>0,"Negative","Neutral"))

In this modified formula, each IF statement checks for the presence of multiple keywords by adding the results of the ISNUMBER(SEARCH(...)) functions. If any of the keywords are found, the corresponding category is assigned.

This approach allows you to have multiple keywords associated with each category, providing more flexibility in categorizing your text data.

Handling Exact Keyword Matches

If you want to ensure that the formula only matches exact keywords and not partial matches, you can modify the formula to include word boundaries. For example:

=IF(ISNUMBER(SEARCH(" great ",A2)),"Positive",IF(ISNUMBER(SEARCH(" excellent ",A2)),"Positive",IF(ISNUMBER(SEARCH(" good ",A2)),"Positive",IF(ISNUMBER(SEARCH(" bad ",A2)),"Negative",IF(ISNUMBER(SEARCH(" poor ",A2)),"Negative",IF(ISNUMBER(SEARCH(" terrible ",A2)),"Negative","Neutral"))))))

In this modified formula, each keyword is surrounded by spaces to ensure that it matches only whole words. This prevents partial matches, such as “greatly” being categorized as “Positive” because it contains the keyword “great”.

Considerations and Limitations

While using Excel formulas to categorize text data based on keywords is a powerful technique, there are a few considerations and limitations to keep in mind:

1. Keyword Selection: The accuracy and effectiveness of the categorization heavily depend on the selection of appropriate keywords. Ensure that the keywords you choose are representative of the categories you want to assign and cover a wide range of possible variations.
2. False Positives and False Negatives: There may be instances where the formula categorizes text incorrectly due to the presence or absence of certain keywords. For example, a comment like “The product is not good” might be categorized as “Positive” because it contains the keyword “good”, even though the sentiment is actually negative. To mitigate this, you can refine your keywords, use more specific phrases, or consider using advanced techniques like sentiment analysis algorithms.
3. Performance: When working with large datasets, the performance of the formula may be impacted, especially if you have a large number of keywords and categories. In such cases, you might consider using other tools or techniques, such as VBA macros or Power Query, to optimize the categorization process.
4. Maintenance: As your dataset evolves and new keywords or categories emerge, you’ll need to update the formula and the keyword table accordingly. Regular maintenance and review of the categorization process are essential to ensure its accuracy and relevance.

Final Thoughts

Categorizing text data in Excel based on keywords is a powerful technique that can save you time and effort when working with large datasets. By leveraging the SEARCH and ISNUMBER functions, you can create a formula that automatically assigns categories to your text data based on the presence of specific keywords.

Remember to customize the keywords and categories according to your specific requirements, and consider using advanced techniques like case-insensitive search, handling multiple keywords per category, and handling exact keyword matches to enhance the flexibility and accuracy of your categorization.

FAQs

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.