Search form

Count the occurrences of a single character in a cell in Excel

This lesson shows you a way to calculate the number of times a single character occurs in a cell in Excel, and provides a real-life example where I needed to split a column of cells containing part numbers into individual components for each part number.

In this example, I was given a spreadsheet with a number of part number patterns. These patterns were off different lengths, and were separated into a number of individual components, each separated by a semi-colon. Part of my job involved figuring out how many components each pattern contained. Here's an example from the spreadsheet I was given:

Microsoft Excel example of part numbers and patterns

As you can see from the example above, each part number is different. In order to split each into its component parts, the first thing I need to do is write a formula to determine how many parts each pattern has.

To do this, I used the following formula:

=LEN(B2)-LEN(SUBSTITUTE(B2,";",""))

Here's how my spreadsheet looked once I'd done this:

Let's break down the formula to see how it works:

=LEN(B2)

This element of the formula counts the number of characters in B2

SUBSTITUTE(B2,";","") This element of the formula uses the SUBSTITUTE() function to replace all of the semi-colons in B2 with nothing (the third element in this formula, "", means "nothing". The result is a text value that doesn't have any semi-colons in it. We can then use the LEN() function again to find out how long the resulting text string is (see below).
LEN(SUBSTITUTE(B2,";","")) By putting the LEN() function around the SUBSTITUTE() function, Excel calculates the length of the text string that is left behind if all the semi-colons are removed. By subtracting this from the first element, which was the length of the original text value, we can work out how many semi-colons were removed.

This may seem more complicated than it needed to be. For a start, I could have simply subtracted LEN(A2) from LEN(B2). However, you won't always have all the data you need (or want) so the formula outlined in this lesson provides a more flexible way to achieve the result you're after.