 SOLVED

# 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

7 Replies

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

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.

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

@MikeWells13  I created a file with a number of useful Lambda Functions and one of them is SumNumsFromTxt

See attached

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

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)),
str & XLOOKUP(chr, inNumers, inNumers, "")
),
)
),
SUM(MAP(range, getNumber))
)
);``````

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

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!

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

@MikeWells13  ah, good catch.  I added that condition and believe it should work now.  You can and should also try @Sergei Baklan  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.

best response confirmed by Hans Vogelaar (MVP)
Solution

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

Here is with blanks

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

Thank you. That's fine exactly what I needed.

Thank you all who have helped me.