Counting one Character as a String
 

Hi I am new to excel and have a problem I would really love help with.

I have multiple characters in one cell separated by spaces. I am trying to count the number of times the letter B appears by itself and not next to any other letter.

eg my data is like: B2 B4 B6 DR1 DR2 DR3 DPB1 DPB2

I only want to count B by itself (ie this would be 3 in above string)

I have used the array formula:
=SUM(LEN(K2)-LEN(SUBSTITUTE(K2,"B","")))/LEN("B")

This works great for all other letters but because I have B and DPB, I get a count of both of these. However, the array formula does work for the DPB entries as I just input "DP".

Is there a clever person out there that can help!!!

Counting one Character as a String
 

More varied examples. This gets 4

Sub countBinCell()
c = Range("k2")
For i = 1 To Len(c)
If UCase(Mid(c, i, 1)) = "B" And _
Not UCase(Mid(c, i + 1, 1)) Like "[A-Z]" Then
mc = mc + 1
End If
If i + 2 = Len(c) Then Exit For
Next i
MsgBox mc
End Sub

On Jan 31, 3:55*pm, sidfictitious <sidfictitious.
wrote:
Hi I am new to excel and have a problem I would really love help with.

I have multiple characters in one cell separated by spaces. I am trying
to count the number of times the letter B appears by itself and not next
to any other letter.

eg my data is like: B2 B4 B6 DR1 DR2 DR3 DPB1 DPB2

I only want to count B by itself (ie this would be 3 in above string)

I have used the array formula:
=SUM(LEN(K2)-LEN(SUBSTITUTE(K2,"B","")))/LEN("B")

This works great for all other letters but because I have B and DPB, I
get a count of both of these. However, the array formula does work for
the DPB entries as I just input "DP".

Is there a clever person out there that can help!!!

--
sidfictitious