How to count the number of occurrences of a character in Excel

Counting the number of occurrences of a character in Excel is a trick that requires you to use a combination of two different functions. To know how to combine this function, please follow the following article of TipsMake.com us.

How to count the number of occurrences of a character in Excel Picture 1

1. How to count the number of occurrences of a character in an Excel data cell

Excel does not have a function that can directly count the number of characters in a data cell. To do this we need to use a combination of the LEN and SUBSTITUTE functions .

1.1 Overview of LEN and SUBSTITUTE functions

The LEN function is an Excel function that counts the number of characters in a parameter cell. Assuming the parameter cell A1 has the characters "123456", then when we use the formula = LEN (A1), we will produce a result of 6 because in A1 contains all 6 characters. Note that LEN will count all characters including special characters or spaces.

How to count the number of occurrences of a character in Excel Picture 2

The SUBSTITUTE function is an alternative function. When using this function for a data cell, it gives you the ability to replace a specific character in a data cell with another character. In addition, we can use SUBSTITUTE to delete specific characters in the data series by leaving blank characters to be replaced.

How to count the number of occurrences of a character in Excel Picture 3

1.2 Combining LEN and SUBSTITUTE functions

We have the LEN function that calculates the number of characters in a string and the SUBSTITUTE function can remove certain characters from the string. From there we can do a simple aggregation.

First we use the LEN function to calculate the number of characters in the original string. Then we use the SUBSTITUTE function to delete the characters we are counting and then continue to use the LEN function to calculate the number of characters in the deleted string.

From there we have two numbers as the original sum along with the sum of the strings that deleted the character. Subtracting the first from the first will calculate the number of occurrences of a character in the original string.

The formula combined here is: = LEN (original string) -LEN (SUBTITUTE (original string; "character to count"; "")

Let's take a look at the example below when using the above formula to find the number of occurrences of a character in Excel.

We want to find the number of occurrences of the letter n in the original character box "Multiplication of the primitive nature". From there the formula is as follows: = LEN (A2) -LEN (SUBSTITUTE (A2; B2; ""))

Press Enter to see results.

How to count the number of occurrences of a character in Excel Picture 4

You can then copy that formula down so you can count the special characters of the example lines below.

How to count the number of occurrences of a character in Excel Picture 5

2. How to count the number of occurrences of a character in an Excel data array

In Part 1 we learned how to combine the LEND and SUBSSTITUTE functions to count the number of occurrences of a character in a data cell.

But if you want to count the number of characters that appear in an array of rows and columns, then we have to use another formula: = SUMPRODUCT (LEN (data array) -LEN (SUBSTITUTE (data array; characters to count; "")))

This formula is similar to the formula in Part 1, but you need to change the data cell to the parameter array data to consider. And use the SUMPRODUCT function to cover all the differences of the two LEN functions .

Based on that as the following example, let's count the number of occurrences of the letter "a" in the data array from A1 to C3. We have the formula: = SUMPRODUCT (LEN (A1: C3) -LEN (SUBSTITUTE (A1: C3; "a"; ""))))

How to count the number of occurrences of a character in Excel Picture 6

When Enter lets Excel perform the calculation, you will see that the result is that there are 8 times the "a" appears in the data array.

How to count the number of occurrences of a character in Excel Picture 7

Thank you for reading our article TipsMake.com on how to count the number of occurrences of a character in Excel. Wish you can do it successfully !!!