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:

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:

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.

Lesson Subject – Excel Formulas and Functions
Related Lessons – 68, 69, 109