cmastrop
cmastrop

Reputation: 1

add/sum different numbers that have a delimiter (space - comma) within the same cell excel

i wanto to sum diferente number delimited by space o comma that are in the same cell eg. A1 en excel and i want a result in another cell eg.A2

A1      A2
1 2 3 = 6

how could o do that?

Upvotes: 0

Views: 906

Answers (2)

Scott Craner
Scott Craner

Reputation: 152505

If one has FILTERXML:

=SUM(FILTERXML("<a><b>"&SUBSTITUTE(TRIM(SUBSTITUTE(A1,","," "))," ","</b><b>")&"</b></a>","//b"))

enter image description here

Upvotes: 2

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

For comma separated values in cell A1, in B1 enter:

=SUM((TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",255)),1+(ROW(A$1:A$999)-1)*255,255)) & "0")/10)

enter image description here

Upvotes: 2

Related Questions