=(Count(Search("(C)",$Y2)))+(Count(Search("(S)",$Y 2)))

Col Y (row 2) C Type 00 Type S Type
sab12334(C) 0056223(00) 1 1 0
sab22233(C)

The above formula is what I'm using to extract certain equipment types from
a single cell. The problem is that it's only counting 1 time. For example,
cell Y2 above has 3 equipment numbers, 2 of which has the (C) at the end, but
I can't get a formula to count the C Type more than once (there can be upto 3
of same type in a cell).

Help!

Hi,

For type (C) use

=(LEN(A1)-LEN(SUBSTITUTE(A1,"(C)","")))/3

for type (00) use

=(LEN(A1)-LEN(SUBSTITUTE(A1,"(00)","")))/4

Mike



"tcbooks" wrote:

=(Count(Search("(C)",$Y2)))+(Count(Search("(S)",$Y 2)))

Col Y (row 2) C Type 00 Type S Type
sab12334(C) 0056223(00) 1 1 0
sab22233(C)

The above formula is what I'm using to extract certain equipment types from
a single cell. The problem is that it's only counting 1 time. For example,
cell Y2 above has 3 equipment numbers, 2 of which has the (C) at the end, but
I can't get a formula to count the C Type more than once (there can be upto 3
of same type in a cell).

Help!

Use this general format...

=(LEN(C2)-LEN(SUBSTITUTE(C2,<YourQuotedText,"")))/<Length

where <YourQuotedText means put the text you are trying to find in
quotes... for example, "(C)"... and where <Length means put the number of
characters in the quoted text (3 for the given example). So, to count how
many (C) are in your cell, use this...

=(LEN(C2)-LEN(SUBSTITUTE(C2,"(C)","")))/3

--
Rick (MVP - Excel)


"tcbooks" wrote in message
...
=(Count(Search("(C)",$Y2)))+(Count(Search("(S)",$Y 2)))

Col Y (row 2) C Type 00 Type S
Type
sab12334(C) 0056223(00) 1 1 0
sab22233(C)

The above formula is what I'm using to extract certain equipment types
from
a single cell. The problem is that it's only counting 1 time. For
example,
cell Y2 above has 3 equipment numbers, 2 of which has the (C) at the end,
but
I can't get a formula to count the C Type more than once (there can be
upto 3
of same type in a cell).

Help!