In this post we will see how to take the sum of all the numerical values separated by a symbol like colon, comma, semicolon , @ etc.
Suppose we have numerical values in a cell separated by a symbol as shown below, we want to take sum of all values separated by a symbol in the next cell.
Step 1
Open VB editor from Developer tab in excel as shown in the figure.
Step 2
Insert a module in which we will write excel function that will calculate the sum of the values separated by a symbol.
Step 3
Now double click the newly created module to open that and paste the below code in the module to create the function.
Function eval(str As String)
eval = Evaluate(str)
End Function
This program will create a user defined function that will scan through the string and calculate the sum of all numeric values separated by a symbol.
Now Enter the formula in cell B1 =eval(SUBSTITUTE(SUBSTITUTE(A1, “@”, “+”),”,”,”.”)) and press Ctrl+Shift_Enter as this is an array formula.
Please note that this formula has “@” as numerical values in the first row is separated by “@” symbol, now when you paste the formula to second row then change replace “@” in the formula with “:” as second row has numerical values separated by colon (:).
Hope this helped.
