Count specific text inside the cell

**Solomon14all** · 10-30-2012, 05:03 AM

Hi everyone,

Can someone guide me how to count specific text inside the cell

for ex B1 CONTAINS 200+300+400,

THERE ARE 6 0'S, 2 +'S.

Now i need formula to do this

Thanks in advance

Raj

**zbor** · 10-30-2012, 05:05 AM

Use this:

=LEN(B1)-LEN(SUBSTITUTE(B1, 0, ""))

or you can write in, for example, C1 0, C2 +, C3 text etc.

And use:

=LEN($B$1)-LEN(SUBSTITUTE($B$1, C1, ""))

and pull down

**benishiryo** · 10-30-2012, 05:09 AM

hi Raj, something like:
="There are "&LEN(B1)-LEN(SUBSTITUTE(B1,0,""))&" 0'S, "&LEN(B1)-LEN(SUBSTITUTE(B1,"+",""))&" +'S"

**Solomon14all** · 10-30-2012, 05:31 AM

your formula was amazing, you gave the exact sentence. Not bad you can do so many things in excel.

**Solomon14all** · 10-30-2012, 05:29 AM

May be i did not make myself very clear. straight to the point.

in B1, Iam etering amount like this, =500+200+300. so B1 adds the amount and becomes 1000. in c1 i want the result. how many "+" is there in b1. now if i put your formula only 0 is appearing.

i think i explained

Thanks for the help
Raj.