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 -