How can I count strings within strings
 

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 !

Hi

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

--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )


"Paul W" wrote in message
...
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 !

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


Pictu
http://www.excelforum.com/attachment...tid=3493&stc=1


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

--
olasa
------------------------------------------------------------------------
olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760
View this thread: http://www.excelforum.com/showthread...hreadid=378921

Hi Arvi,

Why did you say

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

instead of

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

just wondering?

Bob

"Arvi Laanemets" wrote in message
...
Hi

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

--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )


"Paul W" wrote in message
...
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 !

Hi Bob


"Bob Phillips" wrote in message
...
Hi Arvi,

Why did you say

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

instead of

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

just wondering?

:-))) 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!


--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )




Bob

"Arvi Laanemets" wrote in message
...
Hi

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

--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )


"Paul W" wrote in message
...
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 !