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

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

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

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

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

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

"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

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

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

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

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

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

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

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

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

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

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

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

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

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

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