Jan 20 2023 01:11 PM
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
Jan 20 2023 01:47 PM
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.
Jan 20 2023 01:48 PM
@MikeWells13 I created a file with a number of useful Lambda Functions and one of them is SumNumsFromTxt
See attached
Jan 21 2023 06:14 AM
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))
)
);
Jan 21 2023 02:50 PM
Jan 22 2023 10:30 AM
@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.
Jan 24 2023 08:02 AM
SolutionHere is with blanks
Jan 25 2023 11:50 PM