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