Hi

I have an awful feeling that what I am about to ask is not possible but
please have a look.

I have a list of cells which contain:
100 X BLUE BAGS FOR DELIVERY
100 TRADE WASTE BAGS REQUIRED
200 TRADE WASTE BAGS
400 X WASTE BAGS
100 X BLUE WASTE BAGS
etc

but I only want to add the numbers together to see a total amount of bags
ordered but there are quite a few so dont really fancy going through them all
individually to add them so I was hoping there was a simpler way.

Any suggestions would be really greatful

Thanks
Dom

Give this array-entered** formula a try...

=SUM(IF(A1:A1000="",0,--LEFT(A1:A1000,FIND(" ",A1:A1000&" ")-1)))

Change the 3 occurrences of the range to match the range you need to cover.

** Commit this formula using Ctrl+Shift+Enter, not just Enter by itself.

Rick


"Dom" wrote in message
...
Hi

I have an awful feeling that what I am about to ask is not possible but
please have a look.

I have a list of cells which contain:
100 X BLUE BAGS FOR DELIVERY
100 TRADE WASTE BAGS REQUIRED
200 TRADE WASTE BAGS
400 X WASTE BAGS
100 X BLUE WASTE BAGS
etc

but I only want to add the numbers together to see a total amount of bags
ordered but there are quite a few so dont really fancy going through them
all
individually to add them so I was hoping there was a simpler way.

Any suggestions would be really greatful

Thanks
Dom

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


"Rick Rothstein (MVP - VB)" wrote:

Give this array-entered** formula a try...

=SUM(IF(A1:A1000="",0,--LEFT(A1:A1000,FIND(" ",A1:A1000&" ")-1)))

Change the 3 occurrences of the range to match the range you need to cover.

** Commit this formula using Ctrl+Shift+Enter, not just Enter by itself.

Rick


"Dom" wrote in message
...
Hi

I have an awful feeling that what I am about to ask is not possible but
please have a look.

I have a list of cells which contain:
100 X BLUE BAGS FOR DELIVERY
100 TRADE WASTE BAGS REQUIRED
200 TRADE WASTE BAGS
400 X WASTE BAGS
100 X BLUE WASTE BAGS
etc

but I only want to add the numbers together to see a total amount of bags
ordered but there are quite a few so dont really fancy going through them
all
individually to add them so I was hoping there was a simpler way.

Any suggestions would be really greatful

Thanks
Dom

It is possible to write a user-defined function which will extract
only the numerical digits 0-9 from a cell and thus give you a number
(and I think morefunc or ASAP Utilities have this feature). However,
if you had an entry like:

100 BAGS of SAND, 25kg

then you would end up with 10025, so it is difficult to cope with all
situations.

Hope this helps.

Pete

On Jun 26, 10:42*am, 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

"Rick Rothstein (MVP - VB)" wrote:



Give this array-entered** formula a try...

=SUM(IF(A1:A1000="",0,--LEFT(A1:A1000,FIND(" ",A1:A1000&" ")-1)))

Change the 3 occurrences of the range to match the range you need to cover.

** Commit this formula using Ctrl+Shift+Enter, not just Enter by itself..

Rick

"Dom" wrote in message
...
Hi

I have an awful feeling that what I am about to ask is not possible but
please have a look.

I have a list of cells which contain:
100 X BLUE *BAGS FOR DELIVERY
100 TRADE WASTE BAGS REQUIRED
200 TRADE WASTE BAGS
400 X WASTE BAGS
100 X BLUE WASTE BAGS
etc

but I only want to add the numbers together to see a total amount of bags
ordered but there are quite a few so dont really fancy going through them
all
individually to add them so I was hoping there was a simpler way.

Any suggestions would be really greatful

Thanks
Dom- Hide quoted text -

- Show quoted text -

Thanks Pete that sounds like a good solution there should only be 1 entry of
numbers in each cell so it shouldn't cause a problem, does anyone know how to
write a user-defined funtion that I could use?

Thank you all

"Pete_UK" wrote:

It is possible to write a user-defined function which will extract
only the numerical digits 0-9 from a cell and thus give you a number
(and I think morefunc or ASAP Utilities have this feature). However,
if you had an entry like:

100 BAGS of SAND, 25kg

then you would end up with 10025, so it is difficult to cope with all
situations.

Hope this helps.

Pete

On Jun 26, 10:42 am, 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

"Rick Rothstein (MVP - VB)" wrote:



Give this array-entered** formula a try...

=SUM(IF(A1:A1000="",0,--LEFT(A1:A1000,FIND(" ",A1:A1000&" ")-1)))

Change the 3 occurrences of the range to match the range you need to cover.

** Commit this formula using Ctrl+Shift+Enter, not just Enter by itself..

Rick

"Dom" wrote in message
...
Hi

I have an awful feeling that what I am about to ask is not possible but
please have a look.

I have a list of cells which contain:
100 X BLUE BAGS FOR DELIVERY
100 TRADE WASTE BAGS REQUIRED
200 TRADE WASTE BAGS
400 X WASTE BAGS
100 X BLUE WASTE BAGS
etc

but I only want to add the numbers together to see a total amount of bags
ordered but there are quite a few so dont really fancy going through them
all
individually to add them so I was hoping there was a simpler way.

Any suggestions would be really greatful

Thanks
Dom- Hide quoted text -

- Show quoted text -

Try this (for single numeric entry within a string):

Function extrNo(wholestr)
strlen = Len(wholestr)
resultstr = ""
For d = 1 To strlen
currchr = Mid(wholestr, d, 1)
currasc = Asc(currchr)
If currasc = 48 And currasc <= 57 Then resultstr = resultstr &
currchr
Next d
extrNo = resultstr
End Function

Regards,
Stefi

€žDom€� ezt Ã*rta:

Thanks Pete that sounds like a good solution there should only be 1 entry of
numbers in each cell so it shouldn't cause a problem, does anyone know how to
write a user-defined funtion that I could use?

Thank you all

"Pete_UK" wrote:

It is possible to write a user-defined function which will extract
only the numerical digits 0-9 from a cell and thus give you a number
(and I think morefunc or ASAP Utilities have this feature). However,
if you had an entry like:

100 BAGS of SAND, 25kg

then you would end up with 10025, so it is difficult to cope with all
situations.

Hope this helps.

Pete

On Jun 26, 10:42 am, 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

"Rick Rothstein (MVP - VB)" wrote:



Give this array-entered** formula a try...

=SUM(IF(A1:A1000="",0,--LEFT(A1:A1000,FIND(" ",A1:A1000&" ")-1)))

Change the 3 occurrences of the range to match the range you need to cover.

** Commit this formula using Ctrl+Shift+Enter, not just Enter by itself..

Rick

"Dom" wrote in message
...
Hi

I have an awful feeling that what I am about to ask is not possible but
please have a look.

I have a list of cells which contain:
100 X BLUE BAGS FOR DELIVERY
100 TRADE WASTE BAGS REQUIRED
200 TRADE WASTE BAGS
400 X WASTE BAGS
100 X BLUE WASTE BAGS
etc

but I only want to add the numbers together to see a total amount of bags
ordered but there are quite a few so dont really fancy going through them
all
individually to add them so I was hoping there was a simpler way.

Any suggestions would be really greatful

Thanks
Dom- Hide quoted text -

- Show quoted text -

This is a slightly better version:
Function extrNo(wholestr)
strlen = Len(wholestr)
resultstr = ""
firstdigit = False
For d = 1 To strlen
currchr = Mid(wholestr, d, 1)
currasc = Asc(currchr)
If currasc = 48 And currasc <= 57 Then
resultstr = resultstr & currchr
firstdigit = True
Else
If firstdigit Then Exit For
End If
Next d
extrNo = resultstr
End Function

Stefi


€žDom€� ezt Ã*rta:

Thanks Pete that sounds like a good solution there should only be 1 entry of
numbers in each cell so it shouldn't cause a problem, does anyone know how to
write a user-defined funtion that I could use?

Thank you all

"Pete_UK" wrote:

It is possible to write a user-defined function which will extract
only the numerical digits 0-9 from a cell and thus give you a number
(and I think morefunc or ASAP Utilities have this feature). However,
if you had an entry like:

100 BAGS of SAND, 25kg

then you would end up with 10025, so it is difficult to cope with all
situations.

Hope this helps.

Pete

On Jun 26, 10:42 am, 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

"Rick Rothstein (MVP - VB)" wrote:



Give this array-entered** formula a try...

=SUM(IF(A1:A1000="",0,--LEFT(A1:A1000,FIND(" ",A1:A1000&" ")-1)))

Change the 3 occurrences of the range to match the range you need to cover.

** Commit this formula using Ctrl+Shift+Enter, not just Enter by itself..

Rick

"Dom" wrote in message
...
Hi

I have an awful feeling that what I am about to ask is not possible but
please have a look.

I have a list of cells which contain:
100 X BLUE BAGS FOR DELIVERY
100 TRADE WASTE BAGS REQUIRED
200 TRADE WASTE BAGS
400 X WASTE BAGS
100 X BLUE WASTE BAGS
etc

but I only want to add the numbers together to see a total amount of bags
ordered but there are quite a few so dont really fancy going through them
all
individually to add them so I was hoping there was a simpler way.

Any suggestions would be really greatful

Thanks
Dom- Hide quoted text -

- Show quoted text -

Hi

Thanks, unfortunatly something seems to be wrong I have entered that into
the visual basic editor then I have tried to use the function but it is
coming back with a #NAME error. I have double checked to make sure I have
copied everything across correctly and if I am doing anything wrong I am not
sure where.

"Stefi" wrote:

This is a slightly better version:
Function extrNo(wholestr)
strlen = Len(wholestr)
resultstr = ""
firstdigit = False
For d = 1 To strlen
currchr = Mid(wholestr, d, 1)
currasc = Asc(currchr)
If currasc = 48 And currasc <= 57 Then
resultstr = resultstr & currchr
firstdigit = True
Else
If firstdigit Then Exit For
End If
Next d
extrNo = resultstr
End Function

Stefi


€žDom€� ezt Ã*rta:

Thanks Pete that sounds like a good solution there should only be 1 entry of
numbers in each cell so it shouldn't cause a problem, does anyone know how to
write a user-defined funtion that I could use?

Thank you all

"Pete_UK" wrote:

It is possible to write a user-defined function which will extract
only the numerical digits 0-9 from a cell and thus give you a number
(and I think morefunc or ASAP Utilities have this feature). However,
if you had an entry like:

100 BAGS of SAND, 25kg

then you would end up with 10025, so it is difficult to cope with all
situations.

Hope this helps.

Pete

On Jun 26, 10:42 am, 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

"Rick Rothstein (MVP - VB)" wrote:



Give this array-entered** formula a try...

=SUM(IF(A1:A1000="",0,--LEFT(A1:A1000,FIND(" ",A1:A1000&" ")-1)))

Change the 3 occurrences of the range to match the range you need to cover.

** Commit this formula using Ctrl+Shift+Enter, not just Enter by itself..

Rick

"Dom" wrote in message
...
Hi

I have an awful feeling that what I am about to ask is not possible but
please have a look.

I have a list of cells which contain:
100 X BLUE BAGS FOR DELIVERY
100 TRADE WASTE BAGS REQUIRED
200 TRADE WASTE BAGS
400 X WASTE BAGS
100 X BLUE WASTE BAGS
etc

but I only want to add the numbers together to see a total amount of bags
ordered but there are quite a few so dont really fancy going through them
all
individually to add them so I was hoping there was a simpler way.

Any suggestions would be really greatful

Thanks
Dom- Hide quoted text -

- Show quoted text -

1. Make sure the code is placed in a normal module!
2. Check the spelling of the function name extrNo in the cell!

Stefi

€žDom€� ezt Ã*rta:

Hi

Thanks, unfortunatly something seems to be wrong I have entered that into
the visual basic editor then I have tried to use the function but it is
coming back with a #NAME error. I have double checked to make sure I have
copied everything across correctly and if I am doing anything wrong I am not
sure where.

"Stefi" wrote:

This is a slightly better version:
Function extrNo(wholestr)
strlen = Len(wholestr)
resultstr = ""
firstdigit = False
For d = 1 To strlen
currchr = Mid(wholestr, d, 1)
currasc = Asc(currchr)
If currasc = 48 And currasc <= 57 Then
resultstr = resultstr & currchr
firstdigit = True
Else
If firstdigit Then Exit For
End If
Next d
extrNo = resultstr
End Function

Stefi


€žDom€� ezt Ã*rta:

Thanks Pete that sounds like a good solution there should only be 1 entry of
numbers in each cell so it shouldn't cause a problem, does anyone know how to
write a user-defined funtion that I could use?

Thank you all

"Pete_UK" wrote:

It is possible to write a user-defined function which will extract
only the numerical digits 0-9 from a cell and thus give you a number
(and I think morefunc or ASAP Utilities have this feature). However,
if you had an entry like:

100 BAGS of SAND, 25kg

then you would end up with 10025, so it is difficult to cope with all
situations.

Hope this helps.

Pete

On Jun 26, 10:42 am, 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

"Rick Rothstein (MVP - VB)" wrote:



Give this array-entered** formula a try...

=SUM(IF(A1:A1000="",0,--LEFT(A1:A1000,FIND(" ",A1:A1000&" ")-1)))

Change the 3 occurrences of the range to match the range you need to cover.

** Commit this formula using Ctrl+Shift+Enter, not just Enter by itself..

Rick

"Dom" wrote in message
...
Hi

I have an awful feeling that what I am about to ask is not possible but
please have a look.

I have a list of cells which contain:
100 X BLUE BAGS FOR DELIVERY
100 TRADE WASTE BAGS REQUIRED
200 TRADE WASTE BAGS
400 X WASTE BAGS
100 X BLUE WASTE BAGS
etc

but I only want to add the numbers together to see a total amount of bags
ordered but there are quite a few so dont really fancy going through them
all
individually to add them so I was hoping there was a simpler way.

Any suggestions would be really greatful

Thanks
Dom- Hide quoted text -

- Show quoted text -

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

Thanks for your help with this but I am obviously doing something wrong, I
have double checked everything it is in a normal module and i have spelt
everything right.

"Stefi" wrote:

1. Make sure the code is placed in a normal module!
2. Check the spelling of the function name extrNo in the cell!

Stefi

€žDom€� ezt Ã*rta:

Hi

Thanks, unfortunatly something seems to be wrong I have entered that into
the visual basic editor then I have tried to use the function but it is
coming back with a #NAME error. I have double checked to make sure I have
copied everything across correctly and if I am doing anything wrong I am not
sure where.

"Stefi" wrote:

This is a slightly better version:
Function extrNo(wholestr)
strlen = Len(wholestr)
resultstr = ""
firstdigit = False
For d = 1 To strlen
currchr = Mid(wholestr, d, 1)
currasc = Asc(currchr)
If currasc = 48 And currasc <= 57 Then
resultstr = resultstr & currchr
firstdigit = True
Else
If firstdigit Then Exit For
End If
Next d
extrNo = resultstr
End Function

Stefi


€žDom€� ezt Ã*rta:

Thanks Pete that sounds like a good solution there should only be 1 entry of
numbers in each cell so it shouldn't cause a problem, does anyone know how to
write a user-defined funtion that I could use?

Thank you all

"Pete_UK" wrote:

It is possible to write a user-defined function which will extract
only the numerical digits 0-9 from a cell and thus give you a number
(and I think morefunc or ASAP Utilities have this feature). However,
if you had an entry like:

100 BAGS of SAND, 25kg

then you would end up with 10025, so it is difficult to cope with all
situations.

Hope this helps.

Pete

On Jun 26, 10:42 am, 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

"Rick Rothstein (MVP - VB)" wrote:



Give this array-entered** formula a try...

=SUM(IF(A1:A1000="",0,--LEFT(A1:A1000,FIND(" ",A1:A1000&" ")-1)))

Change the 3 occurrences of the range to match the range you need to cover.

** Commit this formula using Ctrl+Shift+Enter, not just Enter by itself..

Rick

"Dom" wrote in message
...
Hi

I have an awful feeling that what I am about to ask is not possible but
please have a look.

I have a list of cells which contain:
100 X BLUE BAGS FOR DELIVERY
100 TRADE WASTE BAGS REQUIRED
200 TRADE WASTE BAGS
400 X WASTE BAGS
100 X BLUE WASTE BAGS
etc

but I only want to add the numbers together to see a total amount of bags
ordered but there are quite a few so dont really fancy going through them
all
individually to add them so I was hoping there was a simpler way.

Any suggestions would be really greatful

Thanks
Dom- Hide quoted text -

- Show quoted text -

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

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

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

Just to keep the archive records complete, here is a non-RegEx UDF solution
that duplicates your results...

Function SumBags(R As Range) As Double
Dim X As Long
Dim C As Range
Dim Sum As Double
For Each C In R
For X = 1 To Len(C.Value)
If Mid(C.Value, X, 1) Like "#" Then
SumBags = SumBags + Val(Mid(Replace(C.Value, ".", "X"), X))
Exit For
End If
Next
Next
End Function

Rick