## Forum Discussion

# Sum formula that ignores characters in a cell that aren't numbers

- Jan 24, 2023
Here is with blanks

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...

- mtarlerApr 19, 2023Silver Contributor
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.

- davidlealApr 19, 2023Iron ContributorThanks, check my updated answer, you need to consider additional non-happy path scenarios.
- mtarlerApr 19, 2023Silver ContributorGood 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))))