Count Specific Characters in a Column in Excel (4 Methods)

Corporate datasets are usually lengthy, and it becomes tedious to maintain and sort out data when necessary. MS Excel has some time-saving tools like SUM, LEN, COUNTIF, etc. functions. This article will demonstrate several methods using suitable excel functions to count specific characters in a column in Excel.


Download Practice Workbook

You can download the practice book from the link below.


4 Methods to Count Specific Characters in a Column in Excel

We will use the following sample dataset throughout the article to describe the 4 methods to count specific characters in a column using SUM, SUMPRODUCT, COUNTIF, and LEN functions.

Sample dataset for excel count specific characters in column

The dataset contains a list of some books in a library. The librarian wants to find out specific book names containing specific character(s) and count them up. Let’s find out how he can do it using Excel functions.


1. Use of the SUMPRODUCT Function to Count Specific Characters in a Column in Excel

SUMPRODUCT is a function that we can nest with other functions like the LEN, SUBSTITUTE, EXACT, ISNUMBER, and FIND functions. Such nested functions can be used to find out the count of specific characters in a column. Let’s explore the functions one by one.

a. Function of SUMPRODUCT with LEN and nested LEN and SUBSTITUTE

We can nest the LEN with SUBSTITUTE and subtract it with the LEN where both are nested in the SUMPRODUCT to form a formula to get the count of specific characters from a column.

Let’s consider that the librarian wants to find the count of “s” characters from the list of book names.

The range here is B5:B9 and the specific character we want to count is “s”.

We will apply the following formula:

=SUMPRODUCT(LEN(B5:B9)-LEN(SUBSTITUTE (B5:B9, "s", "")))

Using SUMPRODUCT with LEN and SUBSTITUTE

Finally, we can see that the character “s” is found 5 times in the range B5:B9.

Explanation of the Nested Function Formula:

The formula we’ve used here can be written in a general form:

=SUMPRODUCT(LEN(range)-LEN(SUBSTITUTE(range, "text", "new_text")

Here the range means the column range where you want to count specific characters from, and the text means the character you want to count.

The new-text part is kept empty as we just want to count the specified characters, not substitute them.

=SUMPRODUCT(LEN(B5:B9)-LEN(SUBSTITUTE(B5:B9, "s", "")))

b. Formula of SUMPRODUCT Nested with EXACT or ISNUMBER and FIND

Again, we can count specific characters from a column using the EXACT function for an exact match of the specific characters or the ISNUMBER and the FIND functions for a partial match, nested inside the SUMPRODUCT function.

Let’s divide this method into two types based on the exact or partial matches.

I. If the librarian wants to find the exact match of the characters from the list let’s say the specific characters are “The Jungle List: A Novel”. For counting for the exact match of these characters we can apply the formula written below:

=SUMPRODUCT(--EXACT(C5, B5:B9))

Using SUMPRODUCT with EXACT function

The list contains the characters 1 time only in the range B5:B9. Hence the result is 1.

Explanation of the Nested Function Formula for This Method:

Here, the syntax of the formula is:

=SUMPRODUCT(--EXACT("text", range)

Here, for the forced conversion of Boolean true/false to the numerical value, we have used the “–” double minus sign. This helps to do further calculations. The EXACT function contains the text which indicates the specific characters that need to be matched. It also contains the range where the characters are to be matched.

Note: In the case of using the EXACT function, the match must be exact to get the result otherwise it won’t show the result for some or a single character. This can be shown below:

Error while Using EXACT match

You can notice using “The” as the characters to be matched gives a result of zero (0) since it cannot find the exact match with only “The” characters.

=SUMPRODUCT(--EXACT(C5, B5:B9))

II. To avoid such sensitive cases, we’ll use partial matches. From the given dataset we will try to count for “S” this time.

The formula for the partial match with the specific character will be:

=SUMPRODUCT(--ISNUMBER(FIND(C5, B5:B9))

Using SUMPRODUCT with ISNUMBER and FIND

Explanation of the Above-Mentioned Formula:

The syntax of the formula:

=SUMPRODUCT(--ISNUMBER (FIND (“text”, range)

Here, the “–” double minus sign is also doing the same conversion of data types and other arguments are also similar. The function used for partial matches is different from the exact one. We’ve used the ISNUMBER and FIND functions here to match the text partially.

Note: For both the cases, the ‘–’ double minus sign indicates that the value it receives will be forced to convert the Boolean (True/False) value to either ‘1’ or ‘0’ to do further maths.

=SUMPRODUCT(--ISNUMBER (FIND (C5, B5:B9))

Read More: How to Count Number of Characters in a Cell in Excel (Easiest 6 Ways)


2. Count Special Characters in a Column in Excel Using the COUNTIF Function

Moreover, to count the number of times the specific character is found in the column, you can use the simple COUNTIF function. Let’s say the librarian wants to find the character “B” from the column. In this case, the formula will be:

Now, there are two ways for this based on finding the character at the beginning or anywhere in the column.

I. Let’s say we want to count for the book names which start with “B”. For this, we need to add “*” at the end of the text in the formula. The formula we need to apply for this is:

=COUNTIF(B5:B9," B*")

Using COUNTIF to find the specific character at the beginning of the data

Since only one book name starts with “B”, the result is 1.

=COUNTIF(B5:B9," B*")

II. However, you might want to find several books where the character “B” is anywhere in the book name. You must put “*” at both the start and end of “B” to get the desired result.

=COUNTIF(B5:B9),"*B*")

Using COUNTIF to find the specific character present anywhere in the data

You can see that the dataset contains two book names that have “B” at the start of a string anywhere in the name. Thus, the result is 2 in this case.

Explanation of COUNTIF Formula for This Method:

The syntax of the formula:

=COUNTIF(range, "text")

Here, the function COUNTIF takes simple arguments. These are the range of the column and specific character text. “*” sign is used either on both sides or at the beginning of the character to get the result for data having the character anywhere inside or at the beginning.

=COUNTIF(B5:B9," *B*")

Read More: Excel Count Specific Characters in Cell (4 Quick Ways)


3. Use of the LEN Function to Count Specific Characters in a Column

Subtracting LEN from nested SUBSTITUTE with LEN can also give the result for counting specific characters in the data of each column. Following is the formula to show how many times “s” is present in the column:

=LEN (B5:B9)-LEN (SUBSTITUTE (B5:B9, "s", "")

The result will show values for each data in the column.

Using LEN with SUBSTITUTE

Explanation of Nested function Formula for this Method:

The syntax of the nested formula:

=LEN(range)-LEN (SUBSTITUTE(range,"text",""))

Here, the LEN function contains the range of the column. The second LEN function takes the SUBSTITUTE function nested with it which contains the range of the column and specific character. The value from this formula is subtracted from the value of the first LEN function to find the count of specific words found in each row of the column.

Note: The formula is case sensitive and so columns having no small letter “s” show zero (0) as result.

=LEN (B5:B9)-LEN (SUBSTITUTE (B5:B9, "s", "")

4. Use the SUM Function to Count Definite Characters in a Column in Excel

For the purpose of counting specific characters in a column, we can also use the SUM function. The formula for finding the count of, say, “T” is:

=SUM(LEFT(B5:B9,1) =" T")*1)

Using SUM with LEFT function

Since there are two columns having “T” at the left, the result is 2.

Explanation of Nested function Formula for this Method:

The syntax of this formula:

=SUM(LEFT(range,[num_chars]), ="text") *1)

Here, the LEFT function is to look up the leftmost character in the column. “num_char” indicates the number of characters it will look up for. The text contains the given specific character. Afterward, multiplying the value by 1 is to convert the Boolean value to a numerical one that is it will give 1*1=1 for Boolean 1 and 1*0=0 for Boolean 0. The result is passed through the SUM function to get the count result.

=SUM(LEFT(B5:B9,1) =" T") *1)

Things to Remember

Evidently, most of the formulas are case-sensitive. Therefore, be careful while applying the methods explained above. If you are not getting the desired result, check for the case of the character.


Conclusion

The article explains four different methods of using Excel formulas to count specific characters in a column in Excel. The formulas include nested versions of SUMPRODUCT, LEN, SUBSTITUTE, and SUM, LEFT. Besides, it also includes COUNTIF. You can use any of the ways which are suitable to your requirements.


Related Articles

Syeda Fahima Nazreen

Syeda Fahima Nazreen

Hello People! This is Syeda Fahima Nazreen. I have completed my Bachelors in Science in Electrical and Electronic Engineering. I love to do research and work anything related to technology which includes research and development. I feel great to share my knowledge with you people and your thoughts and opinions about my writing is highly appreciated by me.

1 Comment
  1. Amazing! Thanks so much for this formula! The 1a did the trick. Thanks again 🙂

Leave a reply

ExcelDemy
Logo