How to count the number of occurrences of a character in Excel?
There are times when you need to count the number of occurrences of a value or character in a spreadsheet. For example, counting the number of workdays, the number of shifts worked, or the number of products sold in a day, a month, etc. If you have a large amount of data, you can’t count manually. In this case, use the COUNT function in Excel to save time and effort. This article from Buffcom.net will guide you on how to use the functions in Excel to count the number of occurrences of a character.
For example, we have the following data table. The requirement is to count the number of times the character “n” appears in those names.
How to count the number of occurrences of a character in Excel?
To perform this task, we need to combine 2 functions: LEN and SUBSTITUTE. First, let’s explore the structure of these 2 functions.
1. LEN function
The LEN function in Excel is used to measure the length of a character string and returns the exact number of characters in the character string, including spaces.
Function syntax: =LEN(text)
Where:
Text: required argument, is the text that you want to count the number of characters.
Note: Spaces are also counted as characters by the function.
Applying to the above example, we just need to enter the formula =LEN(A2) to quickly count the number of characters, including spaces, in cell A2 as 16.
How to count the number of occurrences of a character in Excel?
2. SUBSTITUTE function
The SUBSTITUTE function is used in Excel to replace the old text string with a new text string.
Function syntax: =SUBSTITUTE(text, old_text, new_text, [instance_num])
Where:
- Text: required argument, is the text or cell reference that you want to replace the characters in.
- Old_text: required argument, is the text you want to replace.
- New_text: required argument, is the text you want to replace old_text with.
- Instance_num: optional argument, specifies the number of times you want to replace old_text with new_text.
Applying to the above example, we just need to enter the formula =SUBSTITUTE(A2;B2;””) to quickly remove the character “n” from the name in cell A2.
How to count the number of occurrences of a character in Excel?
3. Count the number of occurrences of a character in a cell
To count the number of times the character “n” appears in a cell, enter the formula as follows:
=LEN(A2)-LEN(SUBSTITUTE(A2;B2;""))
This formula counts the number of characters in cell A2, then subtracts the number of characters in cell A2 after removing the character “n”. This will return the number of times the character “n” appears.
How to count the number of occurrences of a character in Excel?
4. Count the number of occurrences of a character in a data array
If you want to count the number of times the character “n” appears in an entire column of names at once, enter the formula as follows:
=SUMPRODUCT(LEN(A2:A7)-LEN(SUBSTITUTE(A2:A7;"n";"")))
As you can see, the result will return 12, which is the same as the total result returned above.
How to count the number of occurrences of a character in Excel?
So, the article above has guided you on how to count the number of times a character appears in Excel. Hopefully, the article will be useful for you in your work. Wish you success!
