Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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!!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting No of character in a string | Excel Worksheet Functions | |||
Counting specific character in text string | Excel Worksheet Functions | |||
counting instances of a character within a string | Excel Programming | |||
Counting number of time a character appears in a string | Excel Worksheet Functions | |||
Function to return Character Position of Xth character within a string | Excel Programming |