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.