Adding numbers within cells that also contain words

Dom

unread,

Jun 26, 2008, 4:49:01 AM6/26/08

to

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

Rick Rothstein (MVP - VB)

unread,

Jun 26, 2008, 5:16:57 AM6/26/08

to

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" <D...@discussions.microsoft.com> wrote in message
news:627D6C0C-479F-4764...@microsoft.com...

Dom

unread,

Jun 26, 2008, 5:42:01 AM6/26/08

to

Hi Rick

Thanks for that unfortunately that does not work as not all of the cells
start with the number some are:
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

Pete_UK

unread,

Jun 26, 2008, 6:26:06 AM6/26/08

to

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

> > > Dom- Hide quoted text -
>
> - Show quoted text -

Dom

unread,

Jun 26, 2008, 6:35:00 AM6/26/08

to

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 <D...@discussions.microsoft.com> wrote:
> > Hi Rick
> >
> > Thanks for that unfortunately that does not work as not all of the cells
> > start with the number some are:
> > 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..

Stefi

unread,

Jun 26, 2008, 6:58:01 AM6/26/08

to

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:

Stefi

unread,

Jun 26, 2008, 7:02:00 AM6/26/08

to

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:

Dom

unread,

Jun 26, 2008, 7:23:01 AM6/26/08

to

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

unread,

Jun 26, 2008, 9:12:01 AM6/26/08

to

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:

Ron Rosenfeld

unread,

Jun 26, 2008, 9:32:51 AM6/26/08

to

On Thu, 26 Jun 2008 02:42:01 -0700, Dom <D...@discussions.microsoft.com> wrote:

>Hi Rick
>
>Thanks for that unfortunately that does not work as not all of the cells
>start with the number some are:
>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

Dom

unread,

Jun 26, 2008, 9:44:01 AM6/26/08

to

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.

Ron Rosenfeld

unread,

Jun 26, 2008, 10:58:37 AM6/26/08

to

On Thu, 26 Jun 2008 09:32:51 -0400, Ron Rosenfeld <ronros...@nospam.org>
wrote:

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

Dom

unread,

Jun 26, 2008, 11:24:02 AM6/26/08

to

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

Cheers

Rick Rothstein (MVP - VB)

unread,

Jun 26, 2008, 12:16:13 PM6/26/08

to

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