How can I count strings within strings

[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 !

[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...tid=3493&stc=1

[Arvi Laanemets]

Hi

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

--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )


"Paul W" <[email protected]> wrote in message
news:[email protected]...
> 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 !
>
>
>
>
>

[Bob Phillips]

Hi Arvi,

Why did you say

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

instead of

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

just wondering?

Bob

"Arvi Laanemets" <[email protected]> wrote in message
news:[email protected]...
> Hi
>
> =COUNTIF(A1:A96,"*"&"VVN"&"*")
>
> --
> Arvi Laanemets
> ( My real mail address: arvil<at>tarkon.ee )
>
>
> "Paul W" <[email protected]> wrote in message
> news:[email protected]...
> > 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 !
> >
> >
> >
> >
> >
>
>

[Arvi Laanemets]

Hi Bob


"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> 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<at>tarkon.ee )



>
> Bob
>
> "Arvi Laanemets" <[email protected]> wrote in message
> news:[email protected]...
>> Hi
>>
>> =COUNTIF(A1:A96,"*"&"VVN"&"*")
>>
>> --
>> Arvi Laanemets
>> ( My real mail address: arvil<at>tarkon.ee )
>>
>>
>> "Paul W" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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 !
>> >
>> >
>> >
>> >
>> >
>>
>>
>
>