I had an awful feeling that would be the case but thanks for everyones help
anyway

Cheers

"Ron Rosenfeld" wrote:

On Thu, 26 Jun 2008 09:32:51 -0400, Ron Rosenfeld
wrote:

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


If you do want to use a UDF, then:

<alt-F11 opens the VB Editor. Ensure your project is highlighted in the
project explorer window, then select Insert/Module and paste the code below
into the window that opens.

To use this UDF, enter the formula:

=SumBags(range) into some cell where "range" represents where your data might
be.

As written, the routine will extract the first integer (no decimals or
fractions) value from the string, regardless of location, and will sum all of
that in the range.

===============================
Option Explicit
Function SumBags(rg As Range) As Double
Dim c As Range
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.Global = False
re.Pattern = "\d+"

For Each c In rg
If re.test(c.Value) = True Then
Set mc = re.Execute(c.Value)
SumBags = SumBags + mc(0).Value
End If
Next c

End Function
===============================
--ron