S
sidfictitious
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 tryin
to count the number of times the letter B appears by itself and not nex
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,
get a count of both of these. However, the array formula does work fo
the DPB entries as I just input "DP".
Is there a clever person out there that can help!!
I have multiple characters in one cell separated by spaces. I am tryin
to count the number of times the letter B appears by itself and not nex
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,
get a count of both of these. However, the array formula does work fo
the DPB entries as I just input "DP".
Is there a clever person out there that can help!!