Using SEARCH in a single cell to COUNT multiple entries of same te

[tcbooks]

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

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!

 

[Mike H]

Hi,

For type (C) use

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

for type (00) use

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

Mike

 

[Rick Rothstein]

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

 

[tcbooks]

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!