May 12 2021 09:22 AM
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.)
May 12 2021 10:28 AM
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.
May 12 2021 01:25 PM
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.