I have an Excel sheet with a lot of values within a single cell as follows:
a=5
b=10
c=44
d=33
...
I would like to extract to total sum of the values after each =
character.
How can we do that?
Try this small User Defined Function UDF:
Public Function SumInCell(s As String) As Double
Dim s2 As String, d As Variant
s2 = s & ","
ary = Split(s2, "=")
SumInCell = 0
For i = LBound(ary) + 1 To UBound(ary)
s2 = ary(i)
d = CDbl(Mid(s2, 1, InStr(1, s2, ",") - 1))
SumInCell = SumInCell + d
Next i
End Function
For example:
User Defined Functions (UDFs) are very easy to install and use:
If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx
To remove the UDF:
To use the UDF from Excel:
=SumInCell(A1)
To learn more about macros in general, see:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
and
http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx
and for specifics on UDFs, see:
http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx
Macros must be enabled for this to work!