Sum many numbers in one cell

%3CLINGO-SUB%20id%3D%22lingo-sub-2348883%22%20slang%3D%22en-US%22%3ESum%20many%20numbers%20in%20one%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2348883%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20many%20cells%20in%20Word%20each%20containing%20lists%20of%20numbers%20like%20this%3A%203%2C52%2C708%2C21%2C0%2C13%20etc.%20I%20want%20to%20sum%20them%20in%20Word%20or%2C%20after%20converting%2C%20in%20Excel.%20I%20don't%20know%20how%20to%20do%20it%20in%20Word%20or%20how%20to%20convert%20a%20cell%20like%20that%20to%20Excel%20format.%20I%20have%20looked%20at%20many%20suggestions%20but%20so%20far%20none%20tell%20me%20how%26nbsp%3B%20to%20do%20it.%20(In%20the%20enclosed%20file%20I%20want%20each%20cell%20summed%20separately.)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2348883%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2349201%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20many%20numbers%20in%20one%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2349201%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1052210%22%20target%3D%22_blank%22%3E%40Stevebg3%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20removed%20all%20paragraph%20breaks%20in%20cells%20in%20the%20table%2C%20then%20copy%2Fpasted%20the%20table%20into%20a%20new%20Excel%20workbook.%3C%2FP%3E%0A%3CP%3EI%20created%20a%20small%20custom%20function%20to%20compute%20the%20sum%20of%20a%20comma-delimited%20list%20of%20numbers.%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20workbook.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2349720%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20many%20numbers%20in%20one%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2349720%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1052210%22%20target%3D%22_blank%22%3E%40Stevebg3%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%20and%20if%20you%20are%20on%20Excel%20365%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DLET(%0Atxt%2C%20SUBSTITUTE(TRIM(D4)%2C%22%2C%2C%22%2C%20%22%2C%22)%2C%0Asep%2C%20%22%2C%22%2C%0Astr%2C%20IF(RIGHT(txt)%3Dsep%2C%20LEFT(txt%2C%20LEN(txt)-1)%2C%20txt)%2C%0An%2C%20%20%20LEN(str)%2C%0Astart%2C%20LET(%20%20up%2C%200%2C%0A%20%20%20%20%20%20%20%20%20%20k%2C%20%20%20%20SEQUENCE(%2Cn%2Bup%2Cup)%2C%0A%20%20%20%20%20%20%20%20%20%20arr%2C%20IF(k%26gt%3Bn%2C%20%20n%2C%20%20IFERROR(%20IF(%201*(MID(str%2Ck%2C1)%3Dsep)%2C%20k%2B1-2*up%2C%20%200%20)%2C%20%201)%20)%2C%0A%20%20%20%20%20%20%20%20%20%20FILTER(arr%2Carr)%20%20)%2C%0Aend%2C%20%20%20LET(%20%20up%2C%201%2C%0A%20%20%20%20%20%20%20%20%20%20k%2C%20%20%20%20SEQUENCE(%2Cn%2Bup%2Cup)%2C%0A%20%20%20%20%20%20%20%20%20%20arr%2C%20IF(k%26gt%3Bn%2C%20%20n%2C%20%20IFERROR(%20IF(%201*(MID(str%2Ck%2C1)%3Dsep)%2C%20k%2B1-2*up%2C%20%200%20)%2C%20%201)%20)%2C%0A%20%20%20%20%20%20%20%20%20%20FILTER(arr%2Carr)%20%20)%2C%0AIF(str%3D%22%22%2C0%2CSUM(--TRIM(MID(str%2C%20start%2C%20end-start%2B1))))%20)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EFor%20the%20sample%20I%20took%20the%20source%20table%20which%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3Bprepared%20for%20Excel.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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.