|
Suming Cells that contain text data
I need to know if I can sum cells that contain numbers and text in a cell IE
2C + 4T. I want to ignor the text part of the cell data and sum the numbers. Thanks |
Suming Cells that contain text data
Function sumnums(myCell)
c = Len(myCell) For i = 1 To c totals = totals + Val(Mid(myCell, i, 1)) Next sumnums = totals End Function =sumnums(cellref) would return 6 in your example. Gord Dibben MS Excel MVP On Fri, 22 Feb 2008 16:34:39 -0800, Wayne wrote: I need to know if I can sum cells that contain numbers and text in a cell IE 2C + 4T. I want to ignor the text part of the cell data and sum the numbers. Thanks |
Suming Cells that contain text data
On Fri, 22 Feb 2008 16:34:39 -0800, Wayne
wrote: I need to know if I can sum cells that contain numbers and text in a cell IE 2C + 4T. I want to ignor the text part of the cell data and sum the numbers. Thanks This UDF removes all characters except digits, decimal point, and the mathematical operators +-/*^. It then evaluates the expression that remains. This should work on most variations you might use. IT does assume that neither the decimal point nor the mathematical operators appear in the text portion. To enter it, <alt-F11 opens the VBEditor. Ensure your project is highlighted in the project explorer window, then Insert/Module and paste the code below into the window that opens. To use it, enter a formula =EvalNums(cell_ref) into some cell. =================================== Option Explicit Function EvalNums(str As String) As Double Dim re As Object Set re = CreateObject("vbscript.regexp") re.Global = True re.Pattern = "[^0-9+*/^.-]" EvalNums = Evaluate(re.Replace(str, "")) End Function ===================================== --ron |
Suming Cells that contain text data
Okay Thanks to both of you, unfortunately I cant seem to get either option
to work. I am weak in this area of Excel. I get the function entered okay, just keep getting reference errors. For Gords I get a #VALUE! Error if I attempt to reference more than one cell. In my example I should have stated that this is a time sheet therefore I need to sum up K1 to K9. For Rons in the same case I get a Function not valid error. Wayne "Ron Rosenfeld" wrote: On Fri, 22 Feb 2008 16:34:39 -0800, Wayne wrote: I need to know if I can sum cells that contain numbers and text in a cell IE 2C + 4T. I want to ignor the text part of the cell data and sum the numbers. Thanks This UDF removes all characters except digits, decimal point, and the mathematical operators +-/*^. It then evaluates the expression that remains. This should work on most variations you might use. IT does assume that neither the decimal point nor the mathematical operators appear in the text portion. To enter it, <alt-F11 opens the VBEditor. Ensure your project is highlighted in the project explorer window, then Insert/Module and paste the code below into the window that opens. To use it, enter a formula =EvalNums(cell_ref) into some cell. =================================== Option Explicit Function EvalNums(str As String) As Double Dim re As Object Set re = CreateObject("vbscript.regexp") re.Global = True re.Pattern = "[^0-9+*/^.-]" EvalNums = Evaluate(re.Replace(str, "")) End Function ===================================== --ron |
Suming Cells that contain text data
You cannot reference more than one cell with Gord's sumnums UDF or Ron's
evalnums. Ron's evalnums is inconsistent with the Summing as far as I have tested. With 2C + 4T =evalnums(cellref) returns 6 which is correct. While 2CT + 123Y returns 125....2 + 123 =sumnums(cellref) returns 8..........2 + 1 + 2 + 3 Which of these would be considered appropriate? Gord Dibben MS Excel MVP On Fri, 22 Feb 2008 19:09:00 -0800, Wayne wrote: Okay Thanks to both of you, unfortunately I can’t seem to get either option to work. I am weak in this area of Excel. I get the function entered okay, just keep getting reference errors. For Gords I get a #VALUE! Error if I attempt to reference more than one cell. In my example I should have stated that this is a time sheet therefore I need to sum up K1 to K9. For Rons in the same case I get a Function not valid error. Wayne "Ron Rosenfeld" wrote: On Fri, 22 Feb 2008 16:34:39 -0800, Wayne wrote: I need to know if I can sum cells that contain numbers and text in a cell IE 2C + 4T. I want to ignor the text part of the cell data and sum the numbers. Thanks This UDF removes all characters except digits, decimal point, and the mathematical operators +-/*^. It then evaluates the expression that remains. This should work on most variations you might use. IT does assume that neither the decimal point nor the mathematical operators appear in the text portion. To enter it, <alt-F11 opens the VBEditor. Ensure your project is highlighted in the project explorer window, then Insert/Module and paste the code below into the window that opens. To use it, enter a formula =EvalNums(cell_ref) into some cell. =================================== Option Explicit Function EvalNums(str As String) As Double Dim re As Object Set re = CreateObject("vbscript.regexp") re.Global = True re.Pattern = "[^0-9+*/^.-]" EvalNums = Evaluate(re.Replace(str, "")) End Function ===================================== --ron |
Suming Cells that contain text data
On Fri, 22 Feb 2008 19:09:00 -0800, Wayne
wrote: Okay Thanks to both of you, unfortunately I can’t seem to get either option to work. I am weak in this area of Excel. I get the function entered okay, just keep getting reference errors. For Gords I get a #VALUE! Error if I attempt to reference more than one cell. In my example I should have stated that this is a time sheet therefore I need to sum up K1 to K9. For Rons in the same case I get a Function not valid error. In your original post you indicated the string was in "a" cell, implying one cell. That is how my function is designed to work. It is NOT designed to work over a multicell reference. But it will work not only on the example you gave, but also on examples like 14.63CDE + 0.478ABC (again all in one cell). If you have mixed-text and numbers in K1:K9, you should be able to enter my function in an adjacent column in the form =EvalNums(K1) and fill down to K9; then SUM that column. Or you could provide more specific information about your data. Also, I've not seen a "Function not Valid" error. Where do you see that? What version of Excel? In mine, when I select multiple cells, I get a #VALUE! error in the worksheet cell with the formula. --ron |
Suming Cells that contain text data
"Gord Dibben" wrote: You cannot reference more than one cell with Gord's sumnums UDF or Ron's evalnums. Ron's evalnums is inconsistent with the Summing as far as I have tested. With 2C + 4T =evalnums(cellref) returns 6 which is correct. While 2CT + 123Y returns 125....2 + 123 =sumnums(cellref) returns 8..........2 + 1 + 2 + 3 Which of these would be considered appropriate? Both of those would be.. but let me state that it will never be more that 2 digits and 1 letter in any one cell |
Suming Cells that contain text data
Ron,
Can I post files here? I can post the spread sheet that would show exactly what I am attempting to do. I am using MS Office 2007 Pro. Thanks "Ron Rosenfeld" wrote: On Fri, 22 Feb 2008 19:09:00 -0800, Wayne wrote: Okay Thanks to both of you, unfortunately I cant seem to get either option to work. I am weak in this area of Excel. I get the function entered okay, just keep getting reference errors. For Gords I get a #VALUE! Error if I attempt to reference more than one cell. In my example I should have stated that this is a time sheet therefore I need to sum up K1 to K9. For Rons in the same case I get a Function not valid error. In your original post you indicated the string was in "a" cell, implying one cell. That is how my function is designed to work. It is NOT designed to work over a multicell reference. But it will work not only on the example you gave, but also on examples like 14.63CDE + 0.478ABC (again all in one cell). If you have mixed-text and numbers in K1:K9, you should be able to enter my function in an adjacent column in the form =EvalNums(K1) and fill down to K9; then SUM that column. Or you could provide more specific information about your data. Also, I've not seen a "Function not Valid" error. Where do you see that? What version of Excel? In mine, when I select multiple cells, I get a #VALUE! error in the worksheet cell with the formula. --ron |
Suming Cells that contain text data
On Fri, 22 Feb 2008 19:37:52 -0800, Gord Dibben <gorddibbATshawDOTca wrote:
You cannot reference more than one cell with Gord's sumnums UDF or Ron's evalnums. Ron's evalnums is inconsistent with the Summing as far as I have tested. With 2C + 4T =evalnums(cellref) returns 6 which is correct. While 2CT + 123Y returns 125....2 + 123 =sumnums(cellref) returns 8..........2 + 1 + 2 + 3 Which of these would be considered appropriate? Excellent question! You obviously made one assumption, and I made a different assumption. And it looks like we were both incorrect about the data setup! --ron |
Suming Cells that contain text data
On Fri, 22 Feb 2008 19:52:00 -0800, Wayne
wrote: "Gord Dibben" wrote: You cannot reference more than one cell with Gord's sumnums UDF or Ron's evalnums. Ron's evalnums is inconsistent with the Summing as far as I have tested. With 2C + 4T =evalnums(cellref) returns 6 which is correct. While 2CT + 123Y returns 125....2 + 123 =sumnums(cellref) returns 8..........2 + 1 + 2 + 3 Which of these would be considered appropriate? Both of those would be.. but let me state that it will never be more that 2 digits and 1 letter in any one cell I don't think a computer can deal with having two different correct answers to 2CT + 123Y --ron |
Suming Cells that contain text data
On Fri, 22 Feb 2008 19:54:01 -0800, Wayne
wrote: Ron, Can I post files here? I can post the spread sheet that would show exactly what I am attempting to do. I am using MS Office 2007 Pro. Thanks I don't usually open files from the Internet from unknown sources. But you can post your data in text form. If the most you have is one or two digits and a letter in a cell, it shouldn't be too difficult to copy sample values from K1:K9 and also what you expect for a result. --ron |
Suming Cells that contain text data
On Fri, 22 Feb 2008 19:54:01 -0800, Wayne
wrote: Ron, Can I post files here? I can post the spread sheet that would show exactly what I am attempting to do. I am using MS Office 2007 Pro. Thanks I don't usually open files from the Internet from unknown sources. But you can post your data in text form. If the most you have is one or two digits and a letter in a cell, it shouldn't be too difficult to copy sample values from K1:K9 and also what you expect for a result. --ron |
Suming Cells that contain text data
You can easily resolve this dilemma by putting the numbers in one cell and
the letter codes in another cell! =SUM(K1:K9) -- Biff Microsoft Excel MVP "Wayne" wrote in message ... Ron, Can I post files here? I can post the spread sheet that would show exactly what I am attempting to do. I am using MS Office 2007 Pro. Thanks "Ron Rosenfeld" wrote: On Fri, 22 Feb 2008 19:09:00 -0800, Wayne wrote: Okay Thanks to both of you, unfortunately I can't seem to get either option to work. I am weak in this area of Excel. I get the function entered okay, just keep getting reference errors. For Gords I get a #VALUE! Error if I attempt to reference more than one cell. In my example I should have stated that this is a time sheet therefore I need to sum up K1 to K9. For Rons in the same case I get a Function not valid error. In your original post you indicated the string was in "a" cell, implying one cell. That is how my function is designed to work. It is NOT designed to work over a multicell reference. But it will work not only on the example you gave, but also on examples like 14.63CDE + 0.478ABC (again all in one cell). If you have mixed-text and numbers in K1:K9, you should be able to enter my function in an adjacent column in the form =EvalNums(K1) and fill down to K9; then SUM that column. Or you could provide more specific information about your data. Also, I've not seen a "Function not Valid" error. Where do you see that? What version of Excel? In mine, when I select multiple cells, I get a #VALUE! error in the worksheet cell with the formula. --ron |
Suming Cells that contain text data
Wayne
You can post the workbook at one of the hosting sites below. http://www.freefilehosting.net/ http://savefile.com/ When you have uploaded to that site, post the URL so we could download. Gord Dibben MS Excel MVP On Fri, 22 Feb 2008 19:54:01 -0800, Wayne wrote: Ron, Can I post files here? I can post the spread sheet that would show exactly what I am attempting to do. I am using MS Office 2007 Pro. Thanks "Ron Rosenfeld" wrote: On Fri, 22 Feb 2008 19:09:00 -0800, Wayne wrote: Okay Thanks to both of you, unfortunately I can’t seem to get either option to work. I am weak in this area of Excel. I get the function entered okay, just keep getting reference errors. For Gords I get a #VALUE! Error if I attempt to reference more than one cell. In my example I should have stated that this is a time sheet therefore I need to sum up K1 to K9. For Rons in the same case I get a Function not valid error. In your original post you indicated the string was in "a" cell, implying one cell. That is how my function is designed to work. It is NOT designed to work over a multicell reference. But it will work not only on the example you gave, but also on examples like 14.63CDE + 0.478ABC (again all in one cell). If you have mixed-text and numbers in K1:K9, you should be able to enter my function in an adjacent column in the form =EvalNums(K1) and fill down to K9; then SUM that column. Or you could provide more specific information about your data. Also, I've not seen a "Function not Valid" error. Where do you see that? What version of Excel? In mine, when I select multiple cells, I get a #VALUE! error in the worksheet cell with the formula. --ron |
Suming Cells that contain text data
Okay I have removed the 2 functions and will place the file on savefile.com
(waiting on the activation link) I also place the file here, http://www.thez4.com/FileShare/book2.xls Once you open the spreadsheet you will see the top area is the timesheet area. I just spoke with the mgr who is going to be using the timesheet and she explained that the letters indicate the type of OT IE: C=Comp Time : O=Straight OT : S=Stby etc. Column R is the totals column. Rows 9,10 & 11 Columns C:I & K:Q will be the only rows where text will ever appear in a cell and need to be totaled. Row 6 will just have straight hours and therefore doesnt need any modification. They should have presented this info in the first place and T.Valko recommended changing the format of the spreadsheet, that was the first question I asked but they have been using this format for some time and dont want to change it. I wish to extend my thanks for everyone who has offered to help me. PS its been like ten minutes and I still dont have the email so I will post the link once I get it. Wayne "Gord Dibben" wrote: Wayne You can post the workbook at one of the hosting sites below. http://www.freefilehosting.net/ http://savefile.com/ When you have uploaded to that site, post the URL so we could download. Gord Dibben MS Excel MVP On Fri, 22 Feb 2008 19:54:01 -0800, Wayne wrote: Ron, Can I post files here? I can post the spread sheet that would show exactly what I am attempting to do. I am using MS Office 2007 Pro. Thanks "Ron Rosenfeld" wrote: On Fri, 22 Feb 2008 19:09:00 -0800, Wayne wrote: Okay Thanks to both of you, unfortunately I cant seem to get either option to work. I am weak in this area of Excel. I get the function entered okay, just keep getting reference errors. For Gords I get a #VALUE! Error if I attempt to reference more than one cell. In my example I should have stated that this is a time sheet therefore I need to sum up K1 to K9. For Rons in the same case I get a Function not valid error. In your original post you indicated the string was in "a" cell, implying one cell. That is how my function is designed to work. It is NOT designed to work over a multicell reference. But it will work not only on the example you gave, but also on examples like 14.63CDE + 0.478ABC (again all in one cell). If you have mixed-text and numbers in K1:K9, you should be able to enter my function in an adjacent column in the form =EvalNums(K1) and fill down to K9; then SUM that column. Or you could provide more specific information about your data. Also, I've not seen a "Function not Valid" error. Where do you see that? What version of Excel? In mine, when I select multiple cells, I get a #VALUE! error in the worksheet cell with the formula. --ron |
Suming Cells that contain text data
Here is the link to the file on savefile
http://www.savefile.com/files/1398225 "Gord Dibben" wrote: Wayne You can post the workbook at one of the hosting sites below. http://www.freefilehosting.net/ http://savefile.com/ When you have uploaded to that site, post the URL so we could download. Gord Dibben MS Excel MVP On Fri, 22 Feb 2008 19:54:01 -0800, Wayne wrote: Ron, Can I post files here? I can post the spread sheet that would show exactly what I am attempting to do. I am using MS Office 2007 Pro. Thanks "Ron Rosenfeld" wrote: On Fri, 22 Feb 2008 19:09:00 -0800, Wayne wrote: Okay Thanks to both of you, unfortunately I cant seem to get either option to work. I am weak in this area of Excel. I get the function entered okay, just keep getting reference errors. For Gords I get a #VALUE! Error if I attempt to reference more than one cell. In my example I should have stated that this is a time sheet therefore I need to sum up K1 to K9. For Rons in the same case I get a Function not valid error. In your original post you indicated the string was in "a" cell, implying one cell. That is how my function is designed to work. It is NOT designed to work over a multicell reference. But it will work not only on the example you gave, but also on examples like 14.63CDE + 0.478ABC (again all in one cell). If you have mixed-text and numbers in K1:K9, you should be able to enter my function in an adjacent column in the form =EvalNums(K1) and fill down to K9; then SUM that column. Or you could provide more specific information about your data. Also, I've not seen a "Function not Valid" error. Where do you see that? What version of Excel? In mine, when I select multiple cells, I get a #VALUE! error in the worksheet cell with the formula. --ron |
Suming Cells that contain text data
Biff's idea about changing the format is on the mark.
Since STBY and HOT hours are in their own row why bother with the letters in those rows? I would insert one row above current row 10 and split the OT/COM hours into seperate rows for OT and COM Dispense with the letters in those rows also. Gord On Sat, 23 Feb 2008 05:39:00 -0800, Wayne wrote: Okay I have removed the 2 functions and will place the file on savefile.com (waiting on the activation link) I also place the file here, http://www.thez4.com/FileShare/book2.xls Once you open the spreadsheet you will see the top area is the timesheet area. I just spoke with the mgr who is going to be using the timesheet and she explained that the letters indicate the type of OT IE: C=Comp Time : O=Straight OT : S=Stby etc. Column R is the totals column. Rows 9,10 & 11 Columns C:I & K:Q will be the only rows where text will ever appear in a cell and need to be totaled. Row 6 will just have straight hours and therefore doesn’t need any modification. They should have presented this info in the first place and T.Valko recommended changing the format of the spreadsheet, that was the first question I asked but they have been using this format for some time and don’t want to change it. I wish to extend my thanks for everyone who has offered to help me. PS it’s been like ten minutes and I still don’t have the email so I will post the link once I get it. Wayne "Gord Dibben" wrote: Wayne You can post the workbook at one of the hosting sites below. http://www.freefilehosting.net/ http://savefile.com/ When you have uploaded to that site, post the URL so we could download. Gord Dibben MS Excel MVP On Fri, 22 Feb 2008 19:54:01 -0800, Wayne wrote: Ron, Can I post files here? I can post the spread sheet that would show exactly what I am attempting to do. I am using MS Office 2007 Pro. Thanks "Ron Rosenfeld" wrote: On Fri, 22 Feb 2008 19:09:00 -0800, Wayne wrote: Okay Thanks to both of you, unfortunately I can’t seem to get either option to work. I am weak in this area of Excel. I get the function entered okay, just keep getting reference errors. For Gords I get a #VALUE! Error if I attempt to reference more than one cell. In my example I should have stated that this is a time sheet therefore I need to sum up K1 to K9. For Rons in the same case I get a Function not valid error. In your original post you indicated the string was in "a" cell, implying one cell. That is how my function is designed to work. It is NOT designed to work over a multicell reference. But it will work not only on the example you gave, but also on examples like 14.63CDE + 0.478ABC (again all in one cell). If you have mixed-text and numbers in K1:K9, you should be able to enter my function in an adjacent column in the form =EvalNums(K1) and fill down to K9; then SUM that column. Or you could provide more specific information about your data. Also, I've not seen a "Function not Valid" error. Where do you see that? What version of Excel? In mine, when I select multiple cells, I get a #VALUE! error in the worksheet cell with the formula. --ron |
Suming Cells that contain text data
For whatever reason, they dont want to change the spreadsheet - therefore I
am stuck with finding a solution. "Gord Dibben" wrote: Biff's idea about changing the format is on the mark. Since STBY and HOT hours are in their own row why bother with the letters in those rows? I would insert one row above current row 10 and split the OT/COM hours into seperate rows for OT and COM Dispense with the letters in those rows also. Gord On Sat, 23 Feb 2008 05:39:00 -0800, Wayne wrote: Okay I have removed the 2 functions and will place the file on savefile.com (waiting on the activation link) I also place the file here, http://www.thez4.com/FileShare/book2.xls Once you open the spreadsheet you will see the top area is the timesheet area. I just spoke with the mgr who is going to be using the timesheet and she explained that the letters indicate the type of OT IE: C=Comp Time : O=Straight OT : S=Stby etc. Column R is the totals column. Rows 9,10 & 11 Columns C:I & K:Q will be the only rows where text will ever appear in a cell and need to be totaled. Row 6 will just have straight hours and therefore doesnt need any modification. They should have presented this info in the first place and T.Valko recommended changing the format of the spreadsheet, that was the first question I asked but they have been using this format for some time and dont want to change it. I wish to extend my thanks for everyone who has offered to help me. PS its been like ten minutes and I still dont have the email so I will post the link once I get it. Wayne "Gord Dibben" wrote: Wayne You can post the workbook at one of the hosting sites below. http://www.freefilehosting.net/ http://savefile.com/ When you have uploaded to that site, post the URL so we could download. Gord Dibben MS Excel MVP On Fri, 22 Feb 2008 19:54:01 -0800, Wayne wrote: Ron, Can I post files here? I can post the spread sheet that would show exactly what I am attempting to do. I am using MS Office 2007 Pro. Thanks "Ron Rosenfeld" wrote: On Fri, 22 Feb 2008 19:09:00 -0800, Wayne wrote: Okay Thanks to both of you, unfortunately I cant seem to get either option to work. I am weak in this area of Excel. I get the function entered okay, just keep getting reference errors. For Gords I get a #VALUE! Error if I attempt to reference more than one cell. In my example I should have stated that this is a time sheet therefore I need to sum up K1 to K9. For Rons in the same case I get a Function not valid error. In your original post you indicated the string was in "a" cell, implying one cell. That is how my function is designed to work. It is NOT designed to work over a multicell reference. But it will work not only on the example you gave, but also on examples like 14.63CDE + 0.478ABC (again all in one cell). If you have mixed-text and numbers in K1:K9, you should be able to enter my function in an adjacent column in the form =EvalNums(K1) and fill down to K9; then SUM that column. Or you could provide more specific information about your data. Also, I've not seen a "Function not Valid" error. Where do you see that? What version of Excel? In mine, when I select multiple cells, I get a #VALUE! error in the worksheet cell with the formula. --ron |
Suming Cells that contain text data
On rows 9,10 and 11 - when there is an entry will it *ALWAYS* be a number
and *ONE* letter? for example: It *ALWAYS* be 2c...2c...4c...<empty...10c It will *NEVER* be: 2c...2...4c...<empty...10 -- Biff Microsoft Excel MVP "Wayne" wrote in message ... Okay I have removed the 2 functions and will place the file on savefile.com (waiting on the activation link) I also place the file here, http://www.thez4.com/FileShare/book2.xls Once you open the spreadsheet you will see the top area is the timesheet area. I just spoke with the mgr who is going to be using the timesheet and she explained that the letters indicate the type of OT IE: C=Comp Time : O=Straight OT : S=Stby etc. Column R is the totals column. Rows 9,10 & 11 Columns C:I & K:Q will be the only rows where text will ever appear in a cell and need to be totaled. Row 6 will just have straight hours and therefore doesn't need any modification. They should have presented this info in the first place and T.Valko recommended changing the format of the spreadsheet, that was the first question I asked but they have been using this format for some time and don't want to change it. I wish to extend my thanks for everyone who has offered to help me. PS it's been like ten minutes and I still don't have the email so I will post the link once I get it. Wayne "Gord Dibben" wrote: Wayne You can post the workbook at one of the hosting sites below. http://www.freefilehosting.net/ http://savefile.com/ When you have uploaded to that site, post the URL so we could download. Gord Dibben MS Excel MVP On Fri, 22 Feb 2008 19:54:01 -0800, Wayne wrote: Ron, Can I post files here? I can post the spread sheet that would show exactly what I am attempting to do. I am using MS Office 2007 Pro. Thanks "Ron Rosenfeld" wrote: On Fri, 22 Feb 2008 19:09:00 -0800, Wayne wrote: Okay Thanks to both of you, unfortunately I can't seem to get either option to work. I am weak in this area of Excel. I get the function entered okay, just keep getting reference errors. For Gords I get a #VALUE! Error if I attempt to reference more than one cell. In my example I should have stated that this is a time sheet therefore I need to sum up K1 to K9. For Rons in the same case I get a Function not valid error. In your original post you indicated the string was in "a" cell, implying one cell. That is how my function is designed to work. It is NOT designed to work over a multicell reference. But it will work not only on the example you gave, but also on examples like 14.63CDE + 0.478ABC (again all in one cell). If you have mixed-text and numbers in K1:K9, you should be able to enter my function in an adjacent column in the form =EvalNums(K1) and fill down to K9; then SUM that column. Or you could provide more specific information about your data. Also, I've not seen a "Function not Valid" error. Where do you see that? What version of Excel? In mine, when I select multiple cells, I get a #VALUE! error in the worksheet cell with the formula. --ron |
Suming Cells that contain text data
First, change the sumnums UDF to make it a bit easier to type.
Function sn(myCell) c = Len(myCell) For I = 1 To c totals = totals + Val(Mid(myCell, I, 1)) Next sn = totals End Function In R9 enter =SUM(sn(C9),sn(D9),sn(E9),sn(F9),sn(G9).........sn (Q9)) Copy down to R11 Gord On Sat, 23 Feb 2008 11:29:01 -0800, Wayne wrote: For whatever reason, they dont want to change the spreadsheet - therefore I am stuck with finding a solution. "Gord Dibben" wrote: Biff's idea about changing the format is on the mark. Since STBY and HOT hours are in their own row why bother with the letters in those rows? I would insert one row above current row 10 and split the OT/COM hours into seperate rows for OT and COM Dispense with the letters in those rows also. Gord On Sat, 23 Feb 2008 05:39:00 -0800, Wayne wrote: Okay I have removed the 2 functions and will place the file on savefile.com (waiting on the activation link) I also place the file here, http://www.thez4.com/FileShare/book2.xls Once you open the spreadsheet you will see the top area is the timesheet area. I just spoke with the mgr who is going to be using the timesheet and she explained that the letters indicate the type of OT IE: C=Comp Time : O=Straight OT : S=Stby etc. Column R is the totals column. Rows 9,10 & 11 Columns C:I & K:Q will be the only rows where text will ever appear in a cell and need to be totaled. Row 6 will just have straight hours and therefore doesn’t need any modification. They should have presented this info in the first place and T.Valko recommended changing the format of the spreadsheet, that was the first question I asked but they have been using this format for some time and don’t want to change it. I wish to extend my thanks for everyone who has offered to help me. PS it’s been like ten minutes and I still don’t have the email so I will post the link once I get it. Wayne "Gord Dibben" wrote: Wayne You can post the workbook at one of the hosting sites below. http://www.freefilehosting.net/ http://savefile.com/ When you have uploaded to that site, post the URL so we could download. Gord Dibben MS Excel MVP On Fri, 22 Feb 2008 19:54:01 -0800, Wayne wrote: Ron, Can I post files here? I can post the spread sheet that would show exactly what I am attempting to do. I am using MS Office 2007 Pro. Thanks "Ron Rosenfeld" wrote: On Fri, 22 Feb 2008 19:09:00 -0800, Wayne wrote: Okay Thanks to both of you, unfortunately I can’t seem to get either option to work. I am weak in this area of Excel. I get the function entered okay, just keep getting reference errors. For Gords I get a #VALUE! Error if I attempt to reference more than one cell. In my example I should have stated that this is a time sheet therefore I need to sum up K1 to K9. For Rons in the same case I get a Function not valid error. In your original post you indicated the string was in "a" cell, implying one cell. That is how my function is designed to work. It is NOT designed to work over a multicell reference. But it will work not only on the example you gave, but also on examples like 14.63CDE + 0.478ABC (again all in one cell). If you have mixed-text and numbers in K1:K9, you should be able to enter my function in an adjacent column in the form =EvalNums(K1) and fill down to K9; then SUM that column. Or you could provide more specific information about your data. Also, I've not seen a "Function not Valid" error. Where do you see that? What version of Excel? In mine, when I select multiple cells, I get a #VALUE! error in the worksheet cell with the formula. --ron |
Suming Cells that contain text data
I just noticed something...
The section from row 13 to 27 seems to be a summary area where the OT is also entered. The entry on row 15 seems to correspond to the entery on row 9. If this is the case then you can just base your sum on the data in rows 13 to 27. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... On rows 9,10 and 11 - when there is an entry will it *ALWAYS* be a number and *ONE* letter? for example: It *ALWAYS* be 2c...2c...4c...<empty...10c It will *NEVER* be: 2c...2...4c...<empty...10 -- Biff Microsoft Excel MVP "Wayne" wrote in message ... Okay I have removed the 2 functions and will place the file on savefile.com (waiting on the activation link) I also place the file here, http://www.thez4.com/FileShare/book2.xls Once you open the spreadsheet you will see the top area is the timesheet area. I just spoke with the mgr who is going to be using the timesheet and she explained that the letters indicate the type of OT IE: C=Comp Time : O=Straight OT : S=Stby etc. Column R is the totals column. Rows 9,10 & 11 Columns C:I & K:Q will be the only rows where text will ever appear in a cell and need to be totaled. Row 6 will just have straight hours and therefore doesn't need any modification. They should have presented this info in the first place and T.Valko recommended changing the format of the spreadsheet, that was the first question I asked but they have been using this format for some time and don't want to change it. I wish to extend my thanks for everyone who has offered to help me. PS it's been like ten minutes and I still don't have the email so I will post the link once I get it. Wayne "Gord Dibben" wrote: Wayne You can post the workbook at one of the hosting sites below. http://www.freefilehosting.net/ http://savefile.com/ When you have uploaded to that site, post the URL so we could download. Gord Dibben MS Excel MVP On Fri, 22 Feb 2008 19:54:01 -0800, Wayne wrote: Ron, Can I post files here? I can post the spread sheet that would show exactly what I am attempting to do. I am using MS Office 2007 Pro. Thanks "Ron Rosenfeld" wrote: On Fri, 22 Feb 2008 19:09:00 -0800, Wayne wrote: Okay Thanks to both of you, unfortunately I can't seem to get either option to work. I am weak in this area of Excel. I get the function entered okay, just keep getting reference errors. For Gords I get a #VALUE! Error if I attempt to reference more than one cell. In my example I should have stated that this is a time sheet therefore I need to sum up K1 to K9. For Rons in the same case I get a Function not valid error. In your original post you indicated the string was in "a" cell, implying one cell. That is how my function is designed to work. It is NOT designed to work over a multicell reference. But it will work not only on the example you gave, but also on examples like 14.63CDE + 0.478ABC (again all in one cell). If you have mixed-text and numbers in K1:K9, you should be able to enter my function in an adjacent column in the form =EvalNums(K1) and fill down to K9; then SUM that column. Or you could provide more specific information about your data. Also, I've not seen a "Function not Valid" error. Where do you see that? What version of Excel? In mine, when I select multiple cells, I get a #VALUE! error in the worksheet cell with the formula. --ron |
| All times are GMT +1. The time now is 04:32 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com