(Excel) Formula to Sum numbers contained in a String (w/o using 'Evaluate')

Question

I've searched the web but can't wrap my head around it.

• Problem: Individual cell containing string of one or more numbers in percent. If there are multiple numbers, they are separated by a linebreak CHAR(10). Example. cell A1 has this value:

   96%
    4%
    3%
  

• Wanted outcome: Sum up of the numbers irrespective of how many there are. In the above example, result should be 103%.

• Challenge: Must be done via a simple single-cell formula, macros (i.e. '=Evaluate') not possible

Here my formula so far:

=TEXT(IF(ISBLANK(A1),0,
IF(LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))>0,
(SUMPRODUCT(--TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1, ",",""),CHAR(10),REPT(" ",100)),{1,100},100)))),
A1)
),"0.00%")

Where the part in questions is:

SUMPRODUCT(--TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1, ",", ""),CHAR(10),REPT(" ",100)),{1,100},100))))

Which also contains a fail-safe in case a user adds a comma as a separator in addition to the linebreak.

The formula however will always only sum the first two numbers in the string, in this case 96% and 4%... I must admit I'm not comfortable with MID to understand what I am doing wrong.

Thanks in advance!

EDIT: SOLUTION

=SUMPRODUCT(FILTERXML("<h><e>"&SUBSTITUTE(SUBSTITUTE(A1,",",""),CHAR(10),"</e><e>")&"</e></h>","//e"))

Answer 1

Assuming you're using at least Excel 2013, then this must be entered as an array formula (unless you have dynamic arrays)

=SUM(FILTERXML("<h><e>"&SUBSTITUTE(SUBSTITUTE(A1,",",CHAR(10)),CHAR(10),"</e><e>")&"</e></h>","//e"))