Forum Discussion
Sum formula that ignores characters in a cell that aren't numbers
Here is with blanks
MikeWells13 What about this approach?:
=SUM(BYROW(A1:A9,LAMBDA(x,IFERROR(SUM(1*TEXTSPLIT(x,
CHAR(ROW(65:132)),,1)),0))))
It splits by letters, but you can include additional characters if that is your case. Added IFERROR preventing empty cells. Iterate for all rows via BYROW, convert the result to a number via 1*, and sum it. The inner SUM is to consider the case the string can have more than one number, so we sum it, if that is not a use case, then it can be removed.
Maybe it is better to split by numbers, so you don't need to deal with a large list of exclusions (non-numbers), as follows:
=SUM(BYROW(A1:A9, LAMBDA(x,
IFERROR(SUM(1*TEXTSPLIT(x,TEXTSPLIT(x,VSTACK({".";"-"},
CHAR(ROW(48:57))),,1),,1)),IF(ISNUMBER(x),x,0)))))
The inner TEXTSPLIT does it by the characters of our interest: digits plus "." and "-" (to consider negative numbers). Then we use this result for the outer TEXTSPLIT as delimiters to extract just the numbers. We need to treat some special cases: 1) The string is a valid number, or it is just a text with no numbers in such situations TEXTSPLIT returns #CALC!, 2) Empty rows, returns #VALUE!. That is why it was added the IFERROR condition with an IF statement.
Credit to mtarler for a more simplified version from previous formula:
=SUM(BYROW(A1:A9, LAMBDA(x, IFERROR(1*x,SUM(
IFERROR(1*TEXTSPLIT(x,TEXTSPLIT(x,{".";"-"},
CHAR(ROW(48:57)),1),,1),0))))))
It tries to coerce x to a number via 1*, in case the string is a valid number. If Excel can't do it, returns #VALUE!, that is why we use IFERROR. VSTACK is not needed for the inner TEXTSPLIT, we can spit by row and by columns, using both group of delimiters. Then the outer TEXTPLIT tries to use it as list of column delimiters. The second IFERROR is to consider the case the string x has no numbers, "." or "-".
I hope it helps,
David
MikeWells13 i believe both SergeiBaklan and davidleal approaches basically strip out all text and give you a number from concatenating what is left. I think you want/need it to treat each embedded number separately which my attachment above does. here are the Lambda functions from that:
SumNumsFromTxt = LAMBDA(in,
SUM(BYROW(in, LAMBDA(i, SUM(--CleanNumbers(Txt2Array(ExtractNumbers(i), ";"))))))
);
ExtractNumbers = LAMBDA(in,
IF(
ISBLANK(in),
"",
LET(
_s, REDUCE(
";",
MID(in, SEQUENCE(LEN(in)), 1),
LAMBDA(prior, this,
prior &
IFS(
ISNUMBER(SEARCH(this, "0123456789,.")),
this,
RIGHT(prior, 1) = ";",
"",
TRUE,
";"
)
)
),
_m, MID(_s, 2, LEN(_s)),
IF(RIGHT(_m, 1) = ";", LEFT(_m, LEN(_m) - 1), _m)
)
)
);
CleanNumbers = LAMBDA(in,
BYROW(
in,
LAMBDA(i,
IFERROR(
--i,
IFERROR(--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(i, ",", "?"), ".", ","), "?", "."), 0)
)
)
)
);
Txt2Array = LAMBDA(string, delim,
LET(
_L, LEN(string),
_d, IF(ISOMITTED(delim), ",", delim),
_count, _L - LEN(SUBSTITUTE(string, _d, "")),
IF(
_count,
TRIM(MID(SUBSTITUTE(string, _d, REPT(" ", _L)), SEQUENCE(_count + 1, 1, 1, _L), _L)),
string
)
)
);
but as you can see it has quite a bit and especially with newer functions introduced since then could be optimized, but then again, if it works...
- mtarler check my updated answer, I guess the case you are trying to include is more than one number in the string and sum them. I would say it can be achieved by doing a minor modification to my first formula unless I am missing something from your approach. Thanks
I like it. i did that solution before textsplit. However I think it should be:
=LET(in,C1,SUM(BYROW(in,
LAMBDA(x,SUM(IFERROR(1*TEXTSPLIT(x,CHAR(ROW(65:132)),,1),0))))))
and while we are at it maybe make it this to be even more general:
=LET(in,C1,SUM(BYROW(in,
LAMBDA(x,SUM(IFERROR(1*TEXTSPLIT(x,TEXTSPLIT(x,CHAR(ROW(48:57)),,1),,1),0))))))
and if you want to include commas and periods you could add that VSTACK(ROW(48:57),",",".")then I think the only thing my orig solution adds is to check if reversing the ,/. (i.e. swapping number/decimal separators) makes it a recognized number.
- Thanks, check my updated answer, you need to consider additional non-happy path scenarios.