Blog

There is no dedicated function in Excel to count the number of repetitions of a specific character in a cell’s text.
In this step-by-step tutorial, we will learn how to perform this count by combining the SUBSTITUTE functionand LEN function.

Question: Count the number of a specific character in Excel

Assume we want to count the number of times the character \ (backslash) appears in the following text (a file path located in cell A1) and display the result in cell B1.

C:\Users\Pasargad\Pictures\icon

We can do this in the following four steps:

Step 1: Count the total number of characters

Copy the following formula into cell B1 to count the total characters in the text of cell A1.

=LEN(A1)

To learn more about the Excel LEN function, you can visit this page.

Step 2: Remove the character \ (backslash) and create a new text

The SUBSTITUTE function in Excel searches for a character in text and replaces it with a new character. If we use an empty string (“”) as the new character, it effectively removes the original character.
Copy the following formula into cell B2 to remove the \ (backslash) character from the text in cell A1 and create the new text.

=SUBSTITUTE(A1, "\", "")

To learn more about the Excel SUBSTITUTE function, you can visit this page.

Step 3: Count the number of characters in the new text

Copy the following formula into cell B3 to get the number of characters in the text we obtained after removing the \ (backslash) character (cell B2).

=LEN(B2)

Step 4: Count the number of \ in cell A1

Now we have the total character count of the original text in cell B1 and the character count of the text without the backslash in cell B3. The difference between these two values equals the number of \ (backslash) characters in the text of cell A1.

=B1 - B3

Step 5: Combining all formulas into a single formula

The following formula combines all four steps mentioned above. You can use this formula to get the count of a specific character in a cell’s text:

=LEN(A1) - LEN(SUBSTITUTE(A1, "\", ""))