How can I count strings within strings

P

Paul W

I'm trying yo count the number of occurances of a string in a variable string
e.g look for "VVN" in TDCS0/VVN/8375 or mycompany/VVN/8375 or even
mycompany/TDCS0/VVN/8375. The text does not appear in the same location each
time. so what i am looking for is countif(a1/a96="VVN anywhere in this
string")

Excel 2002

Help !
 
O

olasa

To find the number of times VVN occures in one cell:
=(LEN(A2)-LEN(SUBSTITUTE(A2,"VVN","")))/LEN("VVN")

To find the number of times VVN occures in a range:
=SUMPRODUCT((LEN(A2:A4)-LEN(SUBSTITUTE(A2:A4,B1,"")))/LEN(B1))
Range: A2:A4
B1: "VVN"

HTH
Ola Sandström


Picture:
http://www.excelforum.com/attachment.php?attachmentid=3493&stc=1


+-------------------------------------------------------------------+
|Filename: Clipboard01.jpg |
|Download: http://www.excelforum.com/attachment.php?postid=3493 |
+-------------------------------------------------------------------+
 
B

Bob Phillips

Hi Arvi,

Why did you say

=COUNTIF(A1:A96,"*"&"VVN"&"*")

instead of

=COUNTIF(A1:A96,"*VVN*")

just wondering?

Bob
 
A

Arvi Laanemets

Hi Bob


Bob Phillips said:
Hi Arvi,

Why did you say

=COUNTIF(A1:A96,"*"&"VVN"&"*")

instead of

=COUNTIF(A1:A96,"*VVN*")

just wondering?
Click to expand...

:))) Doctors are defining it as 'professinal cretinism', I think :)))

Seriously, I'm a bit busy today, and with my head full my own problems. I
run trough my room for a moment, checked my question in programming NG, and
meanwhile wrote an answer to OP's question. And now I have to run again!
 
Top