count codes in a column w. several codes in a same cell

**chum** · 11-28-2006, 06:32 AM

Hi,

in the attached file there are some codes listed in a column ; there are some cells which have several codes.
The codes are separated wether with a comma, or with a semi-column.
I'd like to count the different codes.
Does anyone has any idea ?

thanks a lot,
Chum.

**Carim** · 11-28-2006, 09:33 AM

Bonjour,

Have you already tried from the menu Data Text to Columns ...?

HTH
Carim

**chum** · 11-28-2006, 09:38 AM

Hi Carim,

yes but the problem is that there are some codes with 3 and some others with 4 characters, so even when I manually delimit the cells, I can not catch all codes correctly.
Do you have another suggestion ?

thanks !
Chum

**Carim** · 11-28-2006, 09:44 AM

Ok then ...

a couple of formulae ...

=LEFT(A1,FIND(",",A1,1)-1)

and

=RIGHT(A1,LEN(A1)-FIND(",",A1,1))

HTH
Carim

**Bryan Hessey** · 11-28-2006, 09:56 AM

Originally Posted by chum

Hi Carim,

yes but the problem is that there are some codes with 3 and some others with 4 characters, so even when I manually delimit the cells, I can not catch all codes correctly.
Do you have another suggestion ?

thanks !
Chum

Hi chum,

In Data, Text to Columns, try Delimited, Comma & SemiColon, then do a cut/paste of column B to the end of column A, repeat for columns C D and E, then Filter, Advanced Filter, Unique, Copy to location.

This will give a unique list, the last row is the count.

hth
---