Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 ! |
#2
|
|||
|
|||
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 ! |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 ! |
#5
|
|||
|
|||
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 ! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
Match Last Occurrence of two numbers and Count to Previous Occurence | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions | |||
SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions |