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
|