I am trying to put together a spreadsheet in Excel 2007 for a specific style of time study. The code strings are varied in their length and go in one cell. An example would be M4P5 M3G1, or "M7P2 M3G3X4" and I need the cells with these codes to be recognized as the sum of the numbers contained within. Is this possible?

Thanks,
DC

I am trying to put together a spreadsheet in Excel 2007 for a specific style of time study. The code strings are varied in their length and go in one cell. An example would be M4P5 M3G1, or "M7P2 M3G3X4" and I need the cells with these codes to be recognized as the sum of the numbers contained within. Is this possible?

Thanks,
DC
Can you expand you question to include a set of example cells and a set of example answers.

Yes, of course

An example would be

Cell C3 "M4G1 M4P5" Total (answer) 14 (4+1+4+5)
Cell C4 "M3G1 M7P5X4" Total (answer) 20 (3+1+7+5+4)

I don't know of a function or formula to do this simply, so I wrote a little User Defined Function to do it for you.

First, here's the code:

Option Explicit

Function AddNumText(txt As Range)
'JBeaucaire (8/3/2009)
'Add all the numbers in a text string together
Dim Val As String, holder As Long, i As Long

Val = txt.Value
holder = 0

For i = 1 To Len(Val)
If IsNumeric(Mid(Val, i, 1)) Then _
holder = holder + Mid(Val, i, 1)
Next i
AddNumText = holder
End Function

How to install the User Defined Function:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The function is installed and ready to use.

If the text string were in A2, then in another cell enter this formula:
=AddNumText(A2)

Hope this helps.

Here's an alternate version that could be used with a RANGE of cells and still work.

Option Explicit

Function AddNumText(txt As Range)
'JBeaucaire (8/3/2009)
'Add all the numbers in a text string together
Dim Val As String, cell As Range, holder As Long, i As Long
holder = 0

For Each cell In txt
Val = cell.Value
For i = 1 To Len(Val)
If IsNumeric(Mid(Val, i, 1)) Then _
holder = holder + Mid(Val, i, 1)
Next i
Next cell

AddNumText = holder
End Function

With this one, you could use this and it would add up the digits in all the cells in the range:
=AddNumText(A2:A10)


The original code in the post above is good for one cell only.

Thank you very much, ill have fun playing with this!