How to Count Occurrences of a Character in String in Excel

Counting character occurrences in Excel is vital for data validation, error correction, and text analysis. It ensures data accuracy, supports formatting, and aids in visual highlighting for quick insights. This versatile tool is fundamental for tasks ranging from data cleaning to advanced analysis, enhancing overall data usability.

In this tutorial, we will discuss how to count the occurrences of a character in the string using some easy-to-use formulas and a user-defined function with VBA.

In this image, we have demonstrated the number of occurrences of characters in a string of some book names by combining the Excel formulas.

To count the occurrences of a character in a string, follow the given examples with proper explanations:

Count Occurrences of a Character in String Considering Letter Cases

Sometimes, it is necessary to find out how many times a particular character appears within an Excel cell. In a situation like this, you can use the LEN and SUBSTITUTE functions simultaneously. The thing is, the SUBSTITUTE function is case-sensitive, so if you look for lower or upper-letter characters, you will only get the count of those characters.

For example, in our dataset, we’re going to count the occurrences of a character in both lowercase and uppercase letters, considering case sensitivity.

To accomplish this task, follow the steps below:

1. Choose the cell where you want the character count to appear.
2. Use the formula:=LEN(B6)-LEN(SUBSTITUTE(B6,C6,""))
   Replace B6 and C6 with the cell references containing your text and the specific character you want to count.
3. Press Enter and drag the Fill Handle icon to autofill the formula downward.

Here’s how the formula works:

The LEN function is used to determine the number of characters in a string. The SUBSTITUTE function replaces specified characters with empty spaces. By subtracting the length of the modified string from the original, you obtain the count of the characters you were looking for.

Finally, you will get the number of all the case-sensitive characters in the above image.

Count Occurrences of a Character in String Ignoring Letter Cases

Unlike the previous method, you may need to count the characters regardless of their case sensitivity. In that case, you can add the UPPER or LOWER function to the previous formula.

To count case-insensitive characters, combine the LEN, SUBSTITUTE, and UPPER functions in a cell:

Insert the following formula:=LEN(B6)-LEN(SUBSTITUTE(UPPER(B6),UPPER(C6),""))
Adjust B6 and C6 to reference the cells containing your text and the specific character you want to count.

Here’s how the formula works:

• This Excel formula is designed to determine the count of occurrences of a specific text in the C6 cell within another text in the B6 cell. The formula first converts both texts to uppercase using the UPPER function to ensure a case-insensitive comparison.
• Then, it substitutes all occurrences of the text in the C6 cell with an empty string in the uppercase version of B5. The difference in the lengths of the original text in the B6 cell and the modified text gives the count of occurrences of the text in the C6 cell within the B6 cell.
• In simpler terms, it calculates how many times the specified text in C6 appears in the original text in B6, regardless of case differences

The above image shows the number of case-insensitive characters.

Count the Number of Occurrences of a Single Character in a Range

Counting single-character occurrences in Excel helps you quickly identify patterns and errors in your data. It also makes data analysis easier and decision-making more informed.

To determine how often a particular character appears within a specified range, follow the two scenarios described below:

Case 1: Using Excel’s Built-in Function

Excel’s built-in character counting function is great for basic needs. It’s easy and efficient if you want a quick and efficient way to count the occurrences of a character in a range without any hassle.

For example, we want to know the frequency of the character ‘A’ or ‘a’ in a data range.

To perform this task, follow the steps below:

1. Type the following formula:=SUM(LEN(B4:B10)-LEN(SUBSTITUTE(B4:B10,"a","")))
   Adjust B4:B10 to reference your data range and specify the character you want to count. In this case, we have specified the character “a” to count the occurrences in the string.
   
2. Here, the SUM function adds all the numbers in a range of cells. The rest of the formula works as before and finally returns the total count of character ‘a’ in the whole range.

Here’s how the formula works:

• In simpler terms, this formula in Excel calculates the total count of the letter “a” across a range of cells (B4 to B10).
• It first replaces all “a” instances in each cell with an empty string, then subtracts the modified length from the original length for each cell, and finally sums up these counts. This gives you the overall count of the letter “a” in the entire range

Case 2: Using a User-Defined Function (UDF)

On the other hand, using a User-Defined Function (UDF) is more suitable for users with specific and customized character-counting requirements.

For those comfortable with basic programming, creating a user-defined function (UDF) can be a powerful solution. Follow these steps:

1. Press Alt+F11 to open the Visual Basic for Applications (VBA) editor.
2. Insert a new module by selecting Insert > Module.
3. Copy and paste the following code into the new Module.

   Function CountCharacter(rng As Range, character As String) As LongDim cell As Range
   Dim charCount As Long
   charCount = 0
   For Each cell In rng
   charCount = charCount + Len(cell.Value) - Len(Replace(cell.Value, character, "")
   Next cell
   CountCharacter = charCount
   End Function

4. After closing the VBA editor, you can use the custom function in your Excel worksheet.
5. Select a cell.
6. Insert the following formula:=CountCharacter(B4:B10,D4)
   Adjust B4:B10 to reference your data range and specify the character you want to count in the D4 cell.
   
7. Press Enter to get the result.

Count Number of Occurrences of Substring in a Range in Excel

Similar to the previous method, you might want to know the frequency of a specific text in a data range. In such situations, you can use combinations of functions. In this method, we combine the SUM, LEN, and SUBSTITUTE functions.

For example, we have a data range containing color names, and you will know the frequency of the color ‘Green’ from the range.

To count the number of occurrences of a substring, use this formula:=SUM(LEN(B4:B10)-LEN(SUBSTITUTE(B4:B10,"Green","")))/LEN("Green")
In the above formula, you need to divide the character count by the specified text or substring Otherwise, each character in the text will be counted individually.

Here’s how the formula works:

• This Excel formula calculates the average number of occurrences of the word “Green” across a range of cells (B4 to B10).
• It replaces all instances of “Green” in each cell with an empty string, then subtracts the modified length from the original length for each cell.
• By summing up these counts and dividing them by the length of “Green,” it provides the average count of how many times “Green” appears in each cell within the specified range.

Note: The formula mentioned above must be entered as an array formula. To enter a formula as an array in Excel for Windows, press CTRL+SHIFT+ENTER to enter a formula as an array.

Download the Workbook

You can download the practice workbook that we have used to prepare this article.

Conclusion

As counting character occurrences in Excel is essential for data validation, error correction, and text analysis, we have shown multiple examples in this article. In this article, you will learn to use formulas and a user-defined function with VBA to count the occurrences of characters in the string. Hopefully, you will find all the methods helpful. If you have any queries regarding these methods, let me know.

Frequently Asked Questions

Can the characters be counted in a cell without including particular letters or symbols?

Yes, you can reduce the character count by excluding particular characters or symbols. Before utilizing the LEN function, remove those characters using the SUBSTITUTE function. To perform this, use the following formula:

=LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, "letter1", ""), "letter2", ""), "letter3", ""))

This formula determines the length of the text in cell A1 while excluding specified characters (‘letter1‘, ‘letter2‘, ‘letter3‘) through nested SUBSTITUTE functions. The LEN function then calculates the resulting length

Are there any shortcuts to quickly count characters in a cell?

While there’s no dedicated keyboard shortcut for character counting, you can quickly see character counts by using the formula bar at the top of the Excel window. When you click on a cell, the formula bar shows you the character count, including spaces.

Can I count characters in cells that are part of a table in Excel?

Yes, you can count characters in cells that are part of an Excel table using the same methods as regular cells. Formulas and functions work similarly within tables.

<< Go Back to Count Characters in Cell | String Manipulation | Learn Excel