5 Ways To Count If Contains Partial Text In Excel

When working with data in Excel, it's not uncommon to need to count cells that contain specific text or phrases. But what if you need to count cells that contain partial text, such as a keyword or a phrase that is part of a larger text string? Fortunately, Excel provides several ways to accomplish this task. In this article, we'll explore five ways to count if contains partial text in Excel.

Why Count Cells with Partial Text?

Before we dive into the methods, let's briefly discuss why counting cells with partial text is important. In many cases, you may need to analyze data that contains text strings with varying lengths and contents. For example, you might need to count the number of cells that contain a specific keyword or phrase in a survey response or customer feedback data. By counting cells with partial text, you can gain valuable insights into your data and make more informed decisions.

Method 1: Using the COUNTIF Function

One of the simplest ways to count cells with partial text is by using the COUNTIF function. This function allows you to count cells that meet specific criteria, including text strings.

The syntax for the COUNTIF function is as follows:

COUNTIF(range, criteria)

Where range is the range of cells you want to count, and criteria is the text string you want to search for.

For example, if you want to count the number of cells in the range A1:A10 that contain the word "example", you would use the following formula:

=COUNTIF(A1:A10, "*example*")

The asterisks (*) are wildcards that match any characters before or after the text string "example".

Method 2: Using the SEARCH Function

Another way to count cells with partial text is by using the SEARCH function in combination with the COUNTIF function. The SEARCH function returns the position of a text string within a cell, and the COUNTIF function can then count the number of cells that contain the text string.

The syntax for the SEARCH function is as follows:

SEARCH(find_text, within_text)

Where find_text is the text string you want to search for, and within_text is the cell or range of cells you want to search.

For example, if you want to count the number of cells in the range A1:A10 that contain the word "example", you would use the following formula:

=COUNTIF(SEARCH("example", A1:A10), ">0")

This formula searches for the word "example" in each cell of the range A1:A10, and returns a value greater than 0 if the text string is found.

Method 3: Using Regular Expressions

Regular expressions are a powerful tool for searching and manipulating text strings. Excel allows you to use regular expressions in the SUBSTITUTE function, which can be used to count cells with partial text.

The syntax for the SUBSTITUTE function with regular expressions is as follows:

SUBSTITUTE(text, find_text, replace_text, [instance_num])

Where text is the cell or range of cells you want to search, find_text is the text string you want to search for, replace_text is the text string you want to replace the found text with, and [instance_num] is the instance of the found text you want to replace.

For example, if you want to count the number of cells in the range A1:A10 that contain the word "example", you would use the following formula:

=COUNTIF(SUBSTITUTE(A1:A10, "example", "found"), "found")

This formula searches for the word "example" in each cell of the range A1:A10, and replaces it with the text string "found". The COUNTIF function then counts the number of cells that contain the text string "found".

Method 4: Using Power Query

Power Query is a powerful data manipulation tool in Excel that allows you to search and filter data using a variety of methods. One way to count cells with partial text using Power Query is by using the Text.Contains function.

The syntax for the Text.Contains function is as follows:

Text.Contains(text, find_text)

Where text is the cell or range of cells you want to search, and find_text is the text string you want to search for.

For example, if you want to count the number of cells in the range A1:A10 that contain the word "example", you would use the following formula:

=COUNTIF(Text.Contains(A1:A10, "example"), TRUE)

This formula searches for the word "example" in each cell of the range A1:A10, and returns a value of TRUE if the text string is found. The COUNTIF function then counts the number of cells that contain the value TRUE.

Method 5: Using VBA Macro

Finally, you can use a VBA macro to count cells with partial text. This method requires some programming knowledge, but can be more flexible and powerful than the other methods.

Here is an example VBA macro that counts cells with partial text:

Sub CountCells()
    Dim rng As Range
    Dim cell As Range
    Dim count As Long
    
    Set rng = Range("A1:A10")
    count = 0
    
    For Each cell In rng
        If InStr(cell.Value, "example") > 0 Then
            count = count + 1
        End If
    Next cell
    
    MsgBox "Number of cells with partial text: " & count
End Sub

This macro searches for the word "example" in each cell of the range A1:A10, and increments a counter variable each time the text string is found. The final count is then displayed in a message box.

Gallery of Printable Excel Functions

Printable Excel Functions Image Gallery

We hope this article has helped you learn five ways to count if contains partial text in Excel. Whether you use the COUNTIF function, regular expressions, or a VBA macro, there are many ways to achieve this task. Do you have any favorite methods for counting cells with partial text in Excel? Share your tips and tricks in the comments below!