How to Count the Occurrences of a Character in a String in Excel – 4 Examples

The image below showcases the number of occurrences of characters in a string in some book names.

Example 1 – Count the Occurrences of a Character in a String Considering Letter Cases

Use the LEN and the SUBSTITUTE function.

The Substitute Function is case-sensitive, so if you look for lower or upper-cases, you will only get the count of those characters.

To count the occurrences of a character in both lowercase and uppercase letters, considering case sensitivity:

• Choose a cell to display the character count.
• 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.
• Press Enter and drag down the Fill Handle.

Formula Breakdown

The LEN function determines 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, it returns the count of the characters you are looking for.

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

Example 2 – Count the Occurrences of a Character in a String Ignoring Letter Cases

Use the UPPER or the LOWER function.

• Enter 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.

Formula Breakdown

• Determines the count of occurrences of a specific text in C6 within another text in B6. The formula converts both texts to uppercase using the UPPER function.
• It substitutes all occurrences of the text in C6 with an empty string in the uppercase version of B5. The difference in the lengths of the original text in B6 and the modified text gives the count of occurrences of the text in C6 within B6.
• It calculates how many times the specified text in C6 appears in the original text in B6, regardless case differences.

Example 3 – Count the Number of Occurrences of a Single Character in a Range

To determine how often a particular character appears within a specified range:

3.1- Using an Excel Built-in Function

To know the frequency of the character ‘A’ or ‘a’ in a data range.

• Enter 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. Here, “a”.
  
• The SUM function adds all the numbers in a range of cells. The rest of the formula works as before and returns the total count of ‘a’ in the whole range.

Formula Breakdown

• Replaces all “a” instances with an empty string, then subtracts the modified length from the original length in each cell, and sums the values.

3.2 – Using a User-Defined Function (UDF)

• Press Alt+F11 to open the Visual Basic for Applications (VBA) editor.
• Insert a new module by selecting Insert > Module.
• 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

• After closing the VBA editor, you can use the custom function in your Excel worksheet.
• Select a cell.
• 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.
  
• Press Enter.

Example 4 – Count the Number of Occurrences of a Substring in a Range in Excel

To know the frequency of the color ‘Green’ :

• Use this formula:

=SUM(LEN(B4:B10)-LEN(SUBSTITUTE(B4:B10,"Green","")))/LEN("Green")

Formula Breakdown

• Calculates the average number of occurrences of the word “Green” in B4:B10.
• It replaces “Green” with an empty string, then subtracts the modified length from the original length in each cell.
• It sums the values and divides them by the length of “Green,”, returning the average count of “Green” within the specified range.

Note: This is an array formula. Press CTRL+SHIFT+ENTER to enter it.

Download the Workbook

Download the practice workbook.

Frequently Asked Questions

Can 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. Use the following formula:

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

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

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

No, but you can quickly see the character count using the formula bar. When you click 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. Formulas and functions work similarly within tables.

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