hi all,

i have a range of data that will be something like this:

1 case
10 cases
100 cases

my cell formula is
=SUMPRODUCT((CustomerOrdersCode=H1)*(formatQuantit y(CustomerOrdersQuantity)))
i am using a cell formula with SUMPRODUCT to determine the total number of
cases
i am having trouble stripping the text from the data and converting it to an
integer
i thought to use a function like:

Public Function formatQuantity(q As Range) As Integer
Dim i As Integer, c
For Each c In q.Cells
For i = 1 To Len(c.value)
If Mid(c.value, i, 1) = " " Then Exit For
Next i
formatQuantity = CInt(Mid(c.value, 1, i))
Next
End Function

i'm not exactly sure how to modify it to accept/return the correct values.

any help - or possibly a better idea?

tia!

J