Forum Discussion
Sum formula that ignores characters in a cell that aren't numbers
Here is with blanks
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.
- Good catch but I would put that check up front. I still think the other IFERROR should be inside the SUM to catch other errors like 2.3.4 will throw an error but then one could ask if an answer excluding that is better than a zero that might be more obvious. In any case another point is we don't need VSTACK and can use the row delimitator instead:
IFERROR(--x,SUM(IFERROR(1*TEXTSPLIT(x,TEXTSPLIT(x,{".";"-"},CHAR(ROW(48:57)),1),,1),0))))Thanks, mtarler It works too. I am surprised that inner TEXTSPLIT doesn't need to put all the delimiters using VSTACK. It works but it should understand the CHAR list of delimiters as row delimiters.
- correct it does. so the inner TEXTSPLIT creates a 2D array of terms to use as delimiters in the outer and the outer doesn't care how those are arranged it will just all of them as either the col or row delims accordingly.