Sum values within a single cell

macropod · #1 05-09-2014, 06:20 PM

You could use a UDF for this:

Code:

Public Function SUMCELLNUMS(Source As Range, StrSplit As String)
Dim StrIn As String, StrTmp As String, ValOut, i As Long
StrIn = Trim(Replace(Source.Text, "'", ""))
For i = 0 To UBound(Split(StrIn, StrSplit))
  StrTmp = Trim(Split(StrIn, StrSplit)(i))
  If IsNumeric(StrTmp) Then
    ValOut = Evaluate(ValOut + StrTmp)
  End If
Next
SUMCELLNUMS = ValOut
End Function

Simply add the above function to a normal code module in the workbook, then use a formula like:
=SUMCELLNUMS(A1, ",")
where A1 is the cell reference and "," is the delimiter.

shabbaranks · #2 05-11-2014, 11:53 PM

Quote:

Originally Posted by macropod

You could use a UDF for this:

Code:

Public Function SUMCELLNUMS(Source As Range, StrSplit As String)
Dim StrIn As String, StrTmp As String, ValOut, i As Long
StrIn = Trim(Replace(Source.Text, "'", ""))
For i = 0 To UBound(Split(StrIn, StrSplit))
  StrTmp = Trim(Split(StrIn, StrSplit)(i))
  If IsNumeric(StrTmp) Then
    ValOut = Evaluate(ValOut + StrTmp)
  End If
Next
SUMCELLNUMS = ValOut
End Function

Simply add the above function to a normal code module in the workbook, then use a formula like:
=SUMCELLNUMS(A1, ",")
where A1 is the cell reference and "," is the delimiter.

Perfecto!! Thanks

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Summing up values in a Single Merge Field	Beowolf	Mail Merge	1	03-07-2014 03:26 PM
Finding Earned Values for Different Baselines in a single project	pratik6312	Project	1	08-22-2013 11:18 AM
How to count multiple values in a single cell, except zero?	iuliandonici	Excel	1	04-13-2011 09:45 PM
Sum & difference between multiple values in a single cell	iuliandonici	Excel	4	04-13-2011 09:27 PM
How can I write the following (whole) formula in a single cell?	Learner7	Excel	1	07-19-2010 10:06 AM