Count specific character in cell 6

[emikoo]

Hi,

Have seen some postings already about this topic, but none answer my question:

Suppose I have a cell (A1) with the following contents:

BCCSSFFGGBSGVZ, and I would like to have a count of "B", (2 in this example.

Any help would be appreciated...preferably not vba as I have no knowledge about that.

Thnxs,

EK

 

[benjamenus]

I have found a method without using VBA but it is not very elegant. Here goes....

CELL A1
contains your text string which is (for instance) a maximum of 3 characters
CELL B1
contains the character you wish to search for
CELL C1
contains the formula =FIND(B1,A1,1) i.e. find the first instance of B1 in A1 and give its position in the string
CELL C2
formula =FIND(B1,A1,C1+1) i.e. find the next instance and give its position
CELL C3
formula =FIND(B1,A1,C2+1) i.e. find the next instance
CELL D1
formula =COUNT(C1:C3)

You need a 'C' cell for all the characters you may have (i.e. 10 characters in string, you need to have C1 to C10) ... in this case

CELL C10 =FIND(B1,A1,C9+1)
CELL D1 =COUNT(C1:C10)

Cell D1 contains the number of occurrences of B1 in your A1 string.

The FIND returns the position of the character in the string (i.e. FIND("this character","in this cell","starting from this position&quot The COUNT checks how many cells returned a value.

Hope I explained this OK, I'll see if anyone comes up with a neater solution

 

[xlbo]

I know you said preferably not VB but it's soooo easy with it

copy this and paste it into a standard module in your workbook

Function Howmany(what As String, where As Range)
testStr = where.Text
ctr = 0
nctsf = Len(what)
For i = 1 To Len(testStr)
If Mid(testStr, i, nctsf) = what Then
ctr = ctr + 1
Else
End If
Next i
Howmany = ctr
End Function

Then, in B1, enter:
=Howmany("B",A1)

It'll even work for different length strings (say you wanted to search for AB or BB etc)

HTH Rgds
~Geoff~

 

[onedtent]

=LEN(A1)-LEN(SUBSTITUTE(A1,"B","&quot)

 

[benjamenus]

Knew there was a better way of doing it, just knowing how. I bow to the man with the brains...

 

[xlbo]

oooh - now that's definately worth a star Rgds
~Geoff~

 

[emikoo]

Very nice!

Thnxs

 

[DaleWatson123321]

Agreed - a GOOD solution. Thanks.

===> another STAR

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[DeLaMartre]

An Elegant solution! Brilliant! -Bob in California

 

[x3]

onedtent,

Just found this forum and it's absolutely great. You're answer was just what I was looking for. Thanks ever so much.

Cole