Suming Cells that contain text data

[Wayne]

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

 

[Gord Dibben]

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

 

[Ron Rosenfeld]

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

 

[Wayne]

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

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

 

[Gord Dibben]

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

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

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

 

[Ron Rosenfeld]

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 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

 

[Wayne]

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]

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

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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

 

[T. Valko]

You can easily resolve this dilemma by putting the numbers in one cell and
the letter codes in another cell!

=SUM(K1:K9)

 

[Gord Dibben]

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

 

[Wayne]

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

 

[Wayne]

Here is the link to the file on savefile
http://www.savefile.com/files/1398225

 

[Gord Dibben]

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

 

[Wayne]

For whatever reason, they dont want to change the spreadsheet - therefore I
am stuck with finding a solution.

 

[T. Valko]

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

 

[Gord Dibben]

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