Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

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

- Home
- Microsoft 365
- Excel
- Re: Sum formula that ignores characters in a cell that aren't numbers

- Subscribe to RSS Feed
- Mark Discussion as New
- Mark Discussion as Read
- Pin this Discussion for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

Labels:

16 Replies

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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))
)
);
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 21 2023 02:50 PM

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!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

best response confirmed by
Hans Vogelaar* (MVP)*

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 24 2023 08:02 AM

SolutionHere is with blanks

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 25 2023 11:50 PM

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

Thank you all who have helped me.

Thank you all who have helped me.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 19 2023 08:25 AM - edited Apr 19 2023 08:28 AM

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:

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 19 2023 08:47 AM - edited Apr 19 2023 06:44 PM

@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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 19 2023 08:57 AM - edited Apr 19 2023 09:02 AM

@MikeWells13 i believe both @Sergei Baklan 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...

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 19 2023 10:04 AM

@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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 19 2023 11:27 AM - edited Apr 19 2023 11:30 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 19 2023 11:51 AM

Thanks, check my updated answer, you need to consider additional non-happy path scenarios.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 19 2023 12:17 PM

Good 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))))

IFERROR(--x,SUM(IFERROR(1*TEXTSPLIT(x,TEXTSPLIT(x,{".";"-"},CHAR(ROW(48:57)),1),,1),0))))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 19 2023 01:16 PM - edited Apr 19 2023 01:19 PM

Thanks, @mtarler It works too. I am surprised that inner TEXTSPLIT doesn't need to put all the delimiters using VSTACK. It works but it should understand the CHAR list of delimiters as row delimiters.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 19 2023 01:36 PM

correct it does. so the inner TEXTSPLIT creates a 2D array of terms to use as delimiters in the outer and the outer doesn't care how those are arranged it will just all of them as either the col or row delims accordingly.