Forum Discussion

Stevebg3's avatar
Stevebg3
Copper Contributor
May 12, 2021

Sum many numbers in one cell

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.)

excel

2 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    May 12, 2021

    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 HansVogelaar prepared for Excel.

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    May 12, 2021

    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.

Resources