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:

- Choose the cell where you want the character count to appear.
- 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 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:

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

- 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:

- 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**cell.

- 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**