Buzz
To count occurrences of a character in Excel, you need to combine two different functions. Follow the article below from Mytour to learn how to do this.
1. Counting occurrences of a character in an Excel cell
Excel does not have a direct function to count the number of characters in a cell. To do this, we need to use a combination of the LEN and SUBSTITUTE functions.
An overview of the LEN and SUBSTITUTE functions
The LEN function in Excel counts the number of characters in a given cell. For example, if cell A1 contains '123456', the formula =LEN(A1) will result in 6 because A1 contains 6 characters. Note that LEN counts all characters including special characters and spaces.
The SUBSTITUTE function is used for replacement. When applied to a cell, it allows you to replace a specific character in the cell with another character. Additionally,
Combining the LEN and SUBSTITUTE functions
We utilize the LEN function to calculate the number of characters in a string and the SUBSTITUTE function to remove specific characters in the string. From there, we can perform a simple calculation.
First, we use the LEN function to count the number of characters in the original string. Then we use the SUBSTITUTE function to remove the characters we want to count, followed by another use of the LEN function to count the characters in the modified string.
This gives us two numbers: the initial total and the total of the string after removing characters. Subtracting the latter from the former gives us the number of times a character appears in the original string.
The combined formula here is: =LEN(original string)-LEN(SUBSTITUTE(original string;'character to count';''))
2. Counting the occurrences of a character in an Excel data array
In Part 1, we learned how to combine the functions LEN and SUBSTITUTE to count the occurrences of a character in a data cell.
But if you want to count the characters appearing in a data array consisting of multiple rows and columns, then we need to use a different formula: =SUMPRODUCT(LEN(data array)-LEN(SUBSTITUTE(data array;character to count;'')))
This formula is similar to the one in Part 1 but you need to replace the data cell with the data array parameter to be examined. And use the SUMPRODUCT function to encompass all differences between two LEN functions.
By using the formula: =SUMPRODUCT(LEN(A1:C3)-LEN(SUBSTITUTE(A1:C3,'a',''))), we find that there are a total of 8 occurrences of the letter 'a' in the data array.
Upon pressing Enter in Excel to perform the calculation, you will observe that the result obtained is a total of 8 instances of the letter 'a' appearing in the data array.
Thank you for reading the article by ExcelHacks.com on counting the occurrences of a character in Excel. Wishing you every success!
