Hi, firstly this could be put into the Functions or General forums, but ultimately I need this coded up in VBA so I'll leave it here. Also, I have the function I want, but there seems to be a problem with entering it via VBA. Anyhoo, on to the problem:

I want to store data in a range of cells which contains different Equipment types, and numbers thereof - with multiple types specified in a single cell. The format is semi-colon and comma delimited, for example;

A1: Equip1, 5; Equip2, 8; Equip4, 3;
A2: Equip3, 1; Equip4, 7; Equip1, 7;

and so on.

Now I have built a function which reads in the string, searches for the Equipment label (eg "Equip1"), finds the corresponding number and then retrieves this string section. Here is my formula, if you can follow it (please advise if you want me to break it down):

=IF(ISERROR(SEARCH(A7, $B$4)), 0, MID($B$4, SEARCH(A7, $B$4)+LEN(A7)+2, IF(ISERROR(SEARCH(";",$B$4,SEARCH(A7, $B$4)+LEN(A7)+2)), LEN($B$4)+1, SEARCH(";",$B$4,SEARCH(A7, $B$4)+LEN(A7)+2))-(SEARCH(A7, $B$4)+LEN(A7)+2)))

In this case A7 holds the search text ("Equip1") and B4 holds the data string.

I then expanded it to an array formula so I can read across many data cells:

{=SUM(IF(ISERROR(SEARCH(A25, $B$20:$B22)), 0, VALUE(MID($B$20:$B22, SEARCH(A25, $B$20:$B22)+LEN(A25)+2, IF(ISERROR(SEARCH(";",$B$20:$B22,SEARCH(A25, $B$20:$B22)+LEN(A25)+2)), LEN($B$20:$B22)+1, SEARCH(";",$B$20:$B22,SEARCH(A25, $B$20:$B22)+LEN(A25)+2))-(SEARCH(A25, $B$20:$B22)+LEN(A25)+2)))))}

Now if you are astute, you will notice this has 8 levels of nesting. When I wrote it I was ignorant of Excel's limitations of only 7 allowed levels. But, if I type this in to Excel it works fine.

I am happy to live with the knowledge that what I have since learned has told me this is impossible, but somehow it works. But the problem I have is when I try to code this in VBA as an .arrayformula I get an error and it won't work.

So my question is this: either can somebody offer me a way to get VBA to write such a function into Excel or, failing that, is there a simpler way to write this function???

Thanks very much in advance.
Peter.