Sum values in text strings across multiple cells

**bhofsetz** · 01-16-2012, 11:25 AM

I'd like to use a formula to sum the values found in text strings across multiple cells.

For example
Cell A1 = 18K,18Mo,18Co
Cell B1 = 2Na,2Si,2K,2Mg,2Fe,2Ca,2Al

I would like to have a formula in C1 that will return 68 (the sum of all the values in these two strings)

The text strings in each cell can be variable length and the numbers can be one, two or three digit numbers but will always be proceeded by a comma (except for the first occurance).

Any help would be greatly appreciated.

Thanks

**Special-K** · 01-16-2012, 12:34 PM

68. The total is 68 not 66

**Special-K** · 01-16-2012, 12:41 PM

I'm wondering if you could use some kind of inline REPLACE to change all letters and commas with a "+" sign, add a 0 to the end and evaluate that.
So you end up with

18+18+18+0
then evaluate that.

Something like EVAL(REPLACE([A...Z,],UPPER(A1),"+")&"0")

Still thinking...

**tom1977** · 01-16-2012, 01:30 PM

check attachment with UDF function

**Winon** · 01-16-2012, 02:55 PM

Hi bhofsetz,

Check the attached Workbook, and see if that will help you. That is all I can come up with now.

Good Luck,

Hope it helps!

**bhofsetz** · 01-16-2012, 05:37 PM

Thanks for the suggestions.

Special-K that is a good idea but anything I was able to come up with would require nesting multiple SUBSTITUTE functions for each letter of the alphabet

tom1977 I was hoping to avoind a UDF but I think that may be the way to to and the one you posted is very straightforward, Thanks!

Winon your suggestion works but I was hoping for something that would automatically pluck the numeric values out of the text string and give the result rather than having to do any manual manipulation.

Thanks again and if anyone else has further suggestions or refinement of anything so far I'd appreciate it.

**bhofsetz** · 01-17-2012, 12:01 PM

Thanks for the UDF tom1977. That is what I was looking for.

My original post asked for handling items in cells A1 and B1 which tom1977's UDF worked perfectly for.

I modified UDF so that it allows you enter a range into the UDF and it will ignore empty cells in that range

Function JobLogSum(a) As Variant
Dim x As Long, expr As Variant, tbl() As Variant, y&, z&
Application.Volatile
For x = 1 To a.Count
    If a(x) <> "" Then
      expr = Split(a(x), ",")
      ReDim Preserve tbl(0 To UBound(expr))
        For y = 0 To UBound(expr)
            tbl(y) = Val(expr(y))
            z = z + Val(expr(y))
        Next y
    End If
Next x

JobLogSum = z

End Function

Thanks again for all the suggestions.