On Thu, 26 Jun 2008 02:42:01 -0700, Dom wrote:

Hi Rick

Thanks for that unfortunately that does not work as not all of the cells
start with the number some a
REQUIRE 200 WASTE BAGS
SUPPLY X200 BLUE WASTE BAGS
etc

I should of shown these on my first post I apologise because some of the
cells are set out completely different is there no way of creating a formula?

Thanks


If you don't want to use a UDF, you could set up a helper column:

If your original data is in column A, use this formula in some blank column to
extract the first "number" from the string:

=LOOKUP(9.9E+307,--MID(A8,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},
A8&"0123456789")),ROW(INDIRECT("1:"&LEN(A8)))))

Fill down as far as required.

Then SUM that column.
--ron