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.
We welcome your comments and questions about this lesson. We don't welcome spam. Our readers get a lot of value out of the comments and answers on our lessons and spam hurts that experience. Our spam filter is pretty good at stopping bots from posting spam, and our admins are quick to delete spam that does get through. We know that bots don't read messages like this, but there are people out there who manually post spam. I repeat - we delete all spam, and if we see repeated posts from a given IP address, we'll block the IP address. So don't waste your time, or ours.