Forum Discussion

MikeWells13's avatar
MikeWells13
Copper Contributor
Jan 20, 2023

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 ignore the letters and only add the numbers (the answer would just be 183, no letters). The number of letters in the cell is not constant. The formula also must not delete the letters as they are counted in a different formula so just remain.

 

Any ideas? Totally stumped.

 

Thanks,

Mike

excel
Formulas and Functions
Macros and VBA
  • davidleal's avatar
    davidleal
    Iron Contributor
    Apr 19, 2023

    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

    • mtarler's avatar
      mtarler
      Silver Contributor
      Apr 19, 2023

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

       

      • davidleal's avatar
        davidleal
        Iron Contributor
        Apr 19, 2023
        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
    • MikeWells13's avatar
      MikeWells13
      Copper Contributor
      Jan 21, 2023
      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!
    • SergeiBaklan's avatar
      SergeiBaklan
      MVP
      Jan 21, 2023

      mtarler 

      As variant

      sumFromTxtRange = LAMBDA(range,
    LET(
        getNumber, LAMBDA(str,
            LET(
                inNumers, CHAR(VSTACK(SEQUENCE(4, , 43), SEQUENCE(10, , 48))),
                chars, LAMBDA(str, MID(str, SEQUENCE(LEN(str)), 1)),
                addChar, LAMBDA(str, chr,
                    str & XLOOKUP(chr, inNumers, inNumers, "")
                ),
                --REDUCE("", chars(str), addChar)
            )
        ),
        SUM(MAP(range, getNumber))
    )
);
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jan 20, 2023

    MikeWells13 

    Copy the following custom VBA function into a standard module in the Visual Basic Editor:

    Function SumNumbers(rng As Range) As Double
    Dim a As Variant
    Dim v As Variant
    Dim s As String
    Dim i As Long
    a = rng.Value
    For Each v In a
        s = ""
        For i = 1 To Len(v)
            If IsNumeric(Mid(v, i, 1)) Then
                s = s & Mid(v, i, 1)
            End If
        Next i
        SumNumbers = SumNumbers + Val(s)
    Next v
End Function

    Switch back to Excel

    Use the function as follows in a cell formula:

    =SumNumbers(A1:A4)

    where A1:A4 is a range with mixed text characters and digits.

     

    Save the workbook as a macro-enabled workbook (*.xlsm).

    Make sure that you allow macros when you open it.

Resources