Sum of*a list of*numbers*recorded*in a single*cell

The problem is the following:
In a single cell (A1) one can find a list of integers, separated by a comma and a space. Numbers can be either positive or negative.
For example: 5, 1, -180, 120, 98, 626, -707, 5000, 20, 14
The sum of all these numbers should be calculated by one formula in one cell.

Solution is found by using the following array formula:

Code:

---

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

---

How it work:

Code:

---

SUBSTITUTE(A1,",",REPT(" ",255))

---

replaces the separator (comma) by 255 spaces, so that each number in the list is inside a sufficiently large empty space.

After that, from the resulting line cut sections containing within itself one number. As it turns out the fact that the length of the number (<16) is much smaller than the length of gap (255 spaces).

Code:

---

TRIM(MID(... ,1+(ROW(A1:A999)-1)*255,255))

---

Whole line is cut to 999 pieces, 255 characters length each, it is done in order not to assume the full amount of numbers in the list. MID function for the initial position more than the length of the string return an empty symbol "".

In carrying out above the string representing the number one of mathematical operations, the string is automatically converted to a number. However, this is not true for blank lines. So " 2 " +0 =2, but ""+0 is error.
To avoid this, each string is concatenated with a zero (& "0"), then the returned strings are divided by 10.

After this it remains only to sum up the resulting number. All generally works well.

---------------------
From russian forum planetaexcel.ru, thread "Усовершенствование формул. Избушка формУлистов"; translated by google