Forum Discussion
Sum formula that ignores characters in a cell that aren't numbers
Hi all, I was wondering if anyone can solve this problem for me. I'm trying to get a formula that will add the something like the following cells: 110a 21xx 7 45bbb The formula needs to ign...
Here is with blanks
Thanks, check my updated answer, you need to consider additional non-happy path scenarios.
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))))
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.
