Im looking to sum the numbers within a cell of text, into another cell.
CodePudding user response：
Try
=sum(split(join("|",REGEXEXTRACT(A1,REGEXREPLACE(A1,"([0-9] )","($1)"))),"|"))
CodePudding user response：
Clear Col C entirely (including the header "TOTAL"). Then place the following formula in cell C1:
=ArrayFormula({"TOTAL"; MMULT(ARRAY_CONSTRAIN(SPLIT(REGEXREPLACE(A2:A,"[^\d] ","~")&REPT("~0",25),"~"),COUNTA(A2:A),25),SEQUENCE(25,1,1,0))})
You can change the header text within the formula itself if you like.
This one formula will produce the header and all results for all rows in Col C.
How It Works
REGEXREPLACE replaces any groupings of anything other than digits with a single tilde (~). To this new string, REPT adds a repeated string of 25 instances of ~0.
SPLIT then splits this at every tilde, which sends each number (and zero appended by REPT into its own column. Some rows at this point will have more columns than others, because you have a different numbers of listed items in each of your A2:A cells.
ARRAY_CONSTRAIN constrains the results into a regular grid with as many rows as there are non-null entries in A2:A and 25 columns. Based on your data samples, this should be more columns than you'd ever have listed items per cell in A2:A. Those REPT zeros will fill in all columns after your last actual value in A2:A.
MMULT can now act on the constrained grid, along with SEQUENCE to do row-by-row addition.