How to sum values separated by semicolon in excel

Question

Closed. This question does not meet Stack Overflow guidelines. It is not currently accepting answers.

---

Want to improve this question? Update the question so it's on-topic for Stack Overflow.

Closed 6 years ago.

I have cell in excel that contains multiple values separated by semicolon, I need to sum each cell.

Number of values in each cell is N (unknown), it can be only one value or it can be 20 or more.

The issue why I have values separated by semicolon is that each row represents product that we are selling. In that row is cell that contains selling prices of that product.
Example Product called "indoor door" were sold three times at different price, so cell contains three values: 102,5;98,2;101,00 Now I need to sum those three values to get total price (at which product was sold).

Answer 1

You can use following formula:

=eval(SUBSTITUTE(SUBSTITUTE(A1, ";", "+"),",","."))

after adding user defined function to your workbook:

Function eval(str As String)
   eval = Evaluate(str)
End Function

Answer 2

you should use text to column tab to separate numbers and put them in separate cells then you can use sum function on them.

Answer 3

Consider the array formula :

=SUM((TRIM(MID(SUBSTITUTE(A1,";",REPT(" ",255)),1+(ROW(A1:A999)-1)*255,255)) & "0")/10)

Array formulas are entered with CNTRL-SHFT-ENTER rather than just the ENTER key.

So if A1 contained:

1;2;3

the formula should return 6