Extract certain parts of string

[Guest]

Sheet contains several cells with assembly numbers that vary according to
type. For instance, here are just two (of many) examples.

RPJU10010D153J5M-N
RCU09314W303JC-N

Notice that the strings vary in overall length, as do the "internal" parts
of the string. For instance, the "RPJU" and "RCU" both indicate a
characteristic of the assembly.

I would like to be able to count how many occurences of "RPJU" there are, as
well as how many contain "10D", etc. Each of these sections of the part
number indicate a different characteristic, so I'm trying to coallate this
into another sheet.

So, how can I sum the instances of these types?

Thanks in advance.

 

[Guest]

If all you are doing is counting, then you can use these formulas

=SUMPRODUCT(IF(ISERROR(FIND("RPJU",A1:A2)),0,1))
and
=SUMPRODUCT(IF(ISERROR(FIND("10D",A1:A2)),0,1))

replace A1:A2 with the actual range. It is an array function, so you will
need to enter them with ctrl+shift+enter.

 

[Guest]

Sheet contains several cells with assembly numbers that vary according to
type. For instance, here are just two (of many) examples.

RPJU10010D153J5M-N
RCU09314W303JC-N

Notice that the strings vary in overall length, as do the "internal" parts
of the string. For instance, the "RPJU" and "RCU" both indicate a
characteristic of the assembly.

I would like to be able to count how many occurences of "RPJU" there are, as
well as how many contain "10D", etc. Each of these sections of the part
number indicate a different characteristic, so I'm trying to coallate this
into another sheet.

So, how can I sum the instances of these types?

many ways to skin a cat, I would try using filtering in your source
worksheet, then go for custom filter for items beginning with xxxx as one
way round it.

You could also split up your cells using MID function in an adjacent column
to bring back the bit you want if the item you want always starts at a set
number of characters from the start / end.

 

[Biff]

Hi!

Try this:

=SUMPRODUCT(--(ISNUMBER(SEARCH("rpju",A1:A100))))

Better to use a cell to hold the criteria:

B1 = rpju

=SUMPRODUCT(--(ISNUMBER(SEARCH(B1,A1:A100))))

Biff