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
MikeWells13 I created a file with a number of useful Lambda Functions and one of them is SumNumsFromTxt
See attached
Thank you for your reply. I've just tested this in my spreadsheet and it works exactly the way I was looking except in one circumstance. In the range I want it to calculate, sometimes some of the cells are blank, but I need it to calculate the rest. Is there a way for this to work without an error (i.e. ignore the blank cells)?
Thanks again. I'm pretty new to this depth of Excel and have never heard of the LAMBDA function before. Not entirely sure how it works!
Thanks again. I'm pretty new to this depth of Excel and have never heard of the LAMBDA function before. Not entirely sure how it works!
Here is with blanks
- Thank you. That's fine exactly what I needed.
Thank you all who have helped me.What if you have multiple numbers in the same cell? Mine keeps trying to multiply other cells instead of adding the cells before, the numbers within that 1 cell, and the cells after for a true sum. Example:
MikeWells13 ah, good catch. I added that condition and believe it should work now. You can and should also try SergeiBaklan solution as his is more concise. Mine function was designed originally to take into account decimal points and commas and european notation vs US so maybe his gets you what you want without all the extras.
