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:
Here's how my spreadsheet looked once I'd done this:
Let's break down the formula to see how it works:
This element of the formula counts the number of characters in 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).|
||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.