Counting
 

I am trying to count the number of times a particular phrase appears in
a cell.

example: "#Call" is what I want to count.

however all the cells are formatted like #Callme-name,

Can any one help...


--
kelljeff
------------------------------------------------------------------------
kelljeff's Profile: http://www.excelforum.com/member.php...o&userid=32007
View this thread: http://www.excelforum.com/showthread...hreadid=525671

Counting
 

Try...

=COUNTIF(A2:A100,"#Call*")

or

=COUNTIF(A2:A100,B2&"*")

....where B2 contains your criteria, such as '#Call'.

Hope this helps!

In article ,
kelljeff wrote:

I am trying to count the number of times a particular phrase appears in
a cell.

example: "#Call" is what I want to count.

however all the cells are formatted like #Callme-name,

Can any one help...

Counting
 

The First worked... Thank you.

Just out of curiosity, I tried that minus the "*" and it did not work.
Does the "*" do something special ?

Again Thank you...


--
kelljeff
------------------------------------------------------------------------
kelljeff's Profile: http://www.excelforum.com/member.php...o&userid=32007
View this thread: http://www.excelforum.com/showthread...hreadid=525671

Counting
 

The "*" is a wildcard character. So basically any cell where the first
5 characters are '#Call' gets counted. If you change the formula to...

=COUNTIF(A2:A100,"*#Call*")

....a cell gets counted when "#Call" occurs anywhere in the string. For
example, cells containing the following strings all get counted...

#Call

#Callxxxxx

xxxx#Call

xxxx#Callxxxx

Hope this helps!

In article ,
kelljeff
wrote:

The First worked... Thank you.

Just out of curiosity, I tried that minus the "*" and it did not work.
Does the "*" do something special ?

Again Thank you...

Counting
 

I used part of this and it got me thinking of how could I count how many
times a character is appears within a CELL. I would like to count the
backward slash character "\". If cell A2 contains "w:\jason\hopson\file.xls"
the number would be 3. Any advise?

"Domenic" wrote:

The "*" is a wildcard character. So basically any cell where the first
5 characters are '#Call' gets counted. If you change the formula to...

=COUNTIF(A2:A100,"*#Call*")

....a cell gets counted when "#Call" occurs anywhere in the string. For
example, cells containing the following strings all get counted...

#Call

#Callxxxxx

xxxx#Call

xxxx#Callxxxx

Hope this helps!

In article ,
kelljeff
wrote:

The First worked... Thank you.

Just out of curiosity, I tried that minus the "*" and it did not work.
Does the "*" do something special ?

Again Thank you...

Counting
 

Try...

=LEN(A2)-LEN(SUBSTITUTE(A2,"\",""))

Hope this helps!

In article ,
Rookie_User wrote:

I used part of this and it got me thinking of how could I count how many
times a character is appears within a CELL. I would like to count the
backward slash character "\". If cell A2 contains "w:\jason\hopson\file.xls"
the number would be 3. Any advise?

Counting
 

Domenic,
It worked perfectly - thank you so much. I just wanted to post a
success/closure. However, if you have time, could you better explain why it
works?

"Domenic" wrote:

Try...

=LEN(A2)-LEN(SUBSTITUTE(A2,"\",""))

Hope this helps!

In article ,
Rookie_User wrote:

I used part of this and it got me thinking of how could I count how many
times a character is appears within a CELL. I would like to count the
backward slash character "\". If cell A2 contains "w:\jason\hopson\file.xls"
the number would be 3. Any advise?

Counting
 

Lets say there are 12 characters in A2 where of 2 are \

LEN(A2)

will count all characters in A2,

now you substitute the 2 \ with 2 "" meaning that the total count would go
from 12 to 10 ("" is counted as empty using LEN)

thus

LEN(SUBSTITUTE(A2,"\",""))

first substitute then count will give 10 then finally subtract that from the
total count gives

=12-10

gives 2 \


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



"Rookie_User" wrote in message
...
Domenic,
It worked perfectly - thank you so much. I just wanted to post a
success/closure. However, if you have time, could you better explain why
it
works?

"Domenic" wrote:

Try...

=LEN(A2)-LEN(SUBSTITUTE(A2,"\",""))

Hope this helps!

In article ,
Rookie_User wrote:

I used part of this and it got me thinking of how could I count how
many
times a character is appears within a CELL. I would like to count the
backward slash character "\". If cell A2 contains
"w:\jason\hopson\file.xls"
the number would be 3. Any advise?