Sum many numbers in one cell

Copper Contributor

I have many cells in Word each containing lists of numbers like this: 3,52,708,21,0,13 etc. I want to sum them in Word or, after converting, in Excel. I don't know how to do it in Word or how to convert a cell like that to Excel format. I have looked at many suggestions but so far none tell me how  to do it. (In the enclosed file I want each cell summed separately.)

2 Replies

@Stevebg3 

I removed all paragraph breaks in cells in the table, then copy/pasted the table into a new Excel workbook.

I created a small custom function to compute the sum of a comma-delimited list of numbers.

See the attached workbook.

@Stevebg3 

As variant and if you are on Excel 365

=LET(
txt, SUBSTITUTE(TRIM(D4),",,", ","),
sep, ",",
str, IF(RIGHT(txt)=sep, LEFT(txt, LEN(txt)-1), txt),
n,   LEN(str),
start, LET(  up, 0,
          k,    SEQUENCE(,n+up,up),
          arr, IF(k>n,  n,  IFERROR( IF( 1*(MID(str,k,1)=sep), k+1-2*up,  0 ),  1) ),
          FILTER(arr,arr)  ),
end,   LET(  up, 1,
          k,    SEQUENCE(,n+up,up),
          arr, IF(k>n,  n,  IFERROR( IF( 1*(MID(str,k,1)=sep), k+1-2*up,  0 ),  1) ),
          FILTER(arr,arr)  ),
IF(str="",0,SUM(--TRIM(MID(str, start, end-start+1)))) )

For the sample I took the source table which @Hans Vogelaar prepared for Excel.