Sum Numbers Separated By Symbol In Excel

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.

Excel showing numerical values separated by symbol




Step 1

Open VB editor from Developer tab in excel as shown in the figure.

Open developer tab to write VBA to calculate sum of numerical values separated by symbol in excel

Step 2

Insert a module in which we will write excel function that will calculate the sum of the values separated by a symbol.

Insert a module in the VB editor

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




excel function to calculate sum of numerical values separated by symbol

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.



Share The Knowledge

Random Posts