Modify UDF to receive and return array

[reece1984]

Hello all,

I have a worksheet that lists the value of various commodities in a single cell. e.g. in Cell A1

3 Piles,
5 Masts,
3 Beams

There are many such cells on a worksheet and I would like to calculate totals for each commodity. To do this my thinking is to find the commodity start character, take the 5 previous characters and strip out only the numbers. To strip out the numbers a user defined function named Cleanup is required.

With the previous text in A1, and Masts in B1, then the the following formula will return the quantity for Masts:
=cleanup(MID($A$1,FIND(C1,$A$1)-5,5))

With cleanup being:

Function CleanUp(txt)
For x = 1 To 255
    Select Case x
        Case 1 To 47, 58 To 255
        txt = WorksheetFunction.Substitute(txt, Chr(x), "")
    End Select
Next x
CleanUp = txt
End Function

The problem is that I need to sum the values using an array formula. And the UDF will not work with an array. Can anybody tell me precisely how to convert it?


The array formula equivalent would calculate the number of masts in cells A1:A3 and is:
=SUM(IF(FIND(C1,$A$1:$A$3)>FIND(",",$A$1:$A$3),cleanup(MID($A$1:$A$3,FIND(C1,$A$1:$A$3)-5,5)),cleanup(LEFT($A$1:$A$3,5))))

Thank you in advance.

James

 

[reece1984]

I have now came up with a solution.