Forum Discussion
what is the problem in this formula
=SUMPRODUCT(IF(ISNUMBER(FILTERXML("<t><s>" & TEXTJOIN("</s><s>", TRUE, H9:H100) & "</s></t>", "//s")), FILTERXML("<t><s>" & TEXTJOIN("</s><s>", TRUE, H9:H100) & "</s></t>", "//s"), 0)) thank you ...
Yes I want to extract numbers like this one 700+400=1100
As variant that could be
=REDUCE(
0,
FILTER(range, range <> "*"),
LAMBDA(a,v,
a + IFERROR(
--LEFT( v,
XMATCH(
FALSE,
ISNUMBER(--MID(v, SEQUENCE(LEN(v)), 1) )
) - 1
), v )
)
)...never used before the "--" in the meaning of "*1" thank you for tip. my version is slightly different and also a bit uglier
=SUM( MAP(range; LAMBDA(iter; IFERROR( LEFT(iter; MAX( IFERROR( XMATCH( SEQUENCE(10;;0)&""; MID(iter; SEQUENCE( LEN(iter);;1);1);0;-1);0)))*1;0))))Thanks for sharing. Didn't test, but I guess your variant takes all text numbers into account, i.e. if the have 1000 QNB2 the latest 2 won't be ignored.
As for the double negations, aka double dashes, that's more or less standard option The double negative in Excel formulas | Exceljet . I personally prefer to use it instead of 1* or +0 or VALUE(), but that's each person choice. Everything in Excel could be done by several ways.
