How to count number of string occurrences in Excel

Have you ever found yourself in a situation when you had a list of, let's say phone numbers, and you wanted to know how many elements contain a certain string in them?

Well, yesterday I found myself in that situation and here's the formula that I found that would do just that:

=SUM(LEN(<range>)-LEN(SUBSTITUTE(<range>,"text","")))/LEN("text")

So, if you had the following data:

A1: (408)111-1111
A2: (408)111-1112
A3: (408)111-1113
A4: (408)111-1114
A5: (408)111-1115
A6: (408)111-1116
A7: (510)111-1111

and you wanted to get count of phone numbers that have area code '408' and count of numbers with area code '510', this is the formula that you'll use:

=SUM(LEN(A1:A7)-LEN(SUBSTITUTE(A1:A7,"408","")))/LEN("408") // produces 6
=SUM(LEN(A1:A7)-LEN(SUBSTITUTE(A1:A7,"510","")))/LEN("510") // produces 1

Please note: You will have to commit your formula by pressing CTRL + SHIFT + ENTER instead of plain ENTER

This information has been taken from Microsoft's website

Comments