Forum Discussion
Finding the right function
there is no magic function to do this. A formula can be created to do it. For example I just remember i wrote a LAMBDA function that will extract numbers from a string of text so that could be used but is probably over-kill. For example is there a consistent format for the name number combination? Is there always a character like - or : in between or at least a space? What about the names, are they single names Jane-3 or could they be multiple like Jane Doe 5
A sample sheet with example data would make it much easier for to answer the question.
that all said I created a formula to do it based on spaces:
=LET(in, A1:A9,
numbers, TOCOL(--TEXTSPLIT(TEXTJOIN(";",1,TRIM(in)), " ", ";",0,,""),3),
names, TRIM(SUBSTITUTE(in,numbers,"")),
nList, UNIQUE(names),
REDUCE({"names","amt"},nList,LAMBDA(p,q, VSTACK(p,HSTACK(q,SUM(FILTER(numbers,names=q,0)))))))
NOTE: I think Niko above assumed you mean you have names in 1 column and values in the next column. Those solutions above work well for that. I assumed a more difficult problem where the cell is text with both the name and number in it:
Mary 5
Sue Beth 18
see attached.