identifying specific data from a string of alphanumeric characters

[koochandkai]

I hope this one doesn't stump you!

I am working with a spreadsheet that has a single field that lists codes for employees working on a given task. For example,
Task A 443A, 5133, SQE
Task B 22A, POP, 4355
Task C 5145, 234, 5124, 513F
etc etc

I want to identify how many times there is a "51xx" code, where x is numeric only -- Task A would have 1, Task B would have 0 and Task C would have 2.

Is there some way to do this using a standard Excel function?

Many thanks.

[Pete_UK]

So where you have those multiple codes (presumably in column B) are they always separated by a comma and a space?

If so, then try this in C1:

=(LEN(" "&B1) - LEN(SUBSTITUTE(" "&B1," 51","")))/3

Hope this helps.

Pete

[FlameRetired]

This array-entered formula seems to do that.

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Row\Col	A	B	C
1	5392	0	In B1 this formula
2	5172	1	=SUM(--ISNUMBER(SEARCH(51&(ROW($1:$10)-1)&TRANSPOSE((ROW($1:$10)-1)),A1)))
3	5227	0
4	5191, 5199	2
5	5036	0
6	5001	0
7	5182	1
8	5289	0
9	5173	1
10	5202	0
11	5318	0
12	5128	1
13	5196	1
14	5252	0