=(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!

Thanks, but I guess I didn't explain very well (not a longtime user
of Excel)..The columns are headed as below, and the Col A2 has many equipment
types in the one cell. See how there are 3 (D) pieces? I can only get 1 to
show in the calculating column for all (D) pieces. I need the calculating
cell to read 4 (3 D and 1 L). This is a small part of my report, but very
important one, and I just can't figure out how to get each piece to count in
the calc cell. There can be 100s of rows with as many as 1 to 8 pieces in
each cell.

Stand Alone Equipment (col A) S & C L,D,E,SA A,6, SH 03,9 etc
0JWWBC) 1 2
1 1
00407B7F34AC(L)
DTPRHZ(D)
DJMLFS(D)
DSTKLP(D)
CED17043(03)
1234(A)

I truly appreciate any help you can give on this one. If I change this one
part then there is a very large change to make to the rest of the raw data.

Thanks!

--
TC Thanks


"JBeaucaire" wrote:

To count how many times a single letter is in an entire cell, use this:

=LEN(Y2)-LEN(SUBSTITUTE(Y2,"C",""))

To do the "double" count for C and S, just double the formula:

=LEN(Y2)-LEN(SUBSTITUTE(Y2,"C",""))+LEN(Y2)-LEN(SUBSTITUTE(Y2,"S",""))

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"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!