|
Adding numbers within cells that also contain words
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 |
Adding numbers within cells that also contain words
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 |
Adding numbers within cells that also contain words
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 |
Adding numbers within cells that also contain words
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 - |
Adding numbers within cells that also contain words
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 - |
Adding numbers within cells that also contain words
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 - |
Adding numbers within cells that also contain words
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 - |
Adding numbers within cells that also contain words
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 - |
Adding numbers within cells that also contain words
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 - |
Adding numbers within cells that also contain words
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 |
Adding numbers within cells that also contain words
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 - |
Adding numbers within cells that also contain words
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 |
Adding numbers within cells that also contain words
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 |
Adding numbers within cells that also contain words
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 |
| All times are GMT +1. The time now is 07:16 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com