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 conv...
excel
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.

Resources