Exclude text from sums

[Be18]

I have to put text into several cells along with numbers, mainly astericks
and some letters, but I also want to sum these cells. How do i sum the
numbers and exclude the text? Please help!!

 

[Toppers]

Use SUM which ignores non-numeric data

 

[bj]

the simple way to do this is to put the numbers by themselves into a range
which you can add
in the display range enter
="wanted text"&[cell with the number]

 

[Be18]

Thank you very much for your comment. However when i try this it excludes the
whole cell but i still need to include the number in that cell. I just want
to exclude the asterisks or letter following the number! Thanks again

 

[Sandy Mann]

Just SUM() them, SUM() ignores text.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Be18]

Thank you for this. However when i do sum it seems to exclude the whole cell
not just the letter!

 

[Bob Phillips]

Add this to an adjacent column, copy down, then sum this columns

=MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},""))))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[driller]

Hi Toppers,
Maybe there is a macro that exist already to clean up unwanted text or
strings or whatever and leave the cell with only the wanted numbers for
summing or averaging or whatever purpose...Ive seen more than a dozen of same
questions in the forum...
or maybe u can do it simply...

regards again...

 

[Sandy Mann]

Then how about a UDF?

Enter the following code in a general module:

Function AddItUp(Range_to_add As Range)
Dim cell As Range
Dim x As Integer
Dim cVal As Double
Dim Tot As Double

For Each cell In Range_to_add
For x = 1 To Len(cell)
If IsNumeric(Mid(cell.Value, x, 1)) Then
cVal = cVal * 10 + Mid(cell.Value, x, 1)
End If
Next x
Tot = Tot + cVal
cVal = 0
Next cell

AddItUp = Tot
End Function


Then enterin the worksheet
=AddItUp(A1:A10)
or whatever the range is

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Sandy Mann]

Bob,

Your formula returns text numbers which of course SUM() to zero. If I
convert them to numbers by adding a double unary in front of the formula I
get #VALUE! for all text cells.

If I use your formula and try to add them up using a SUMPRODUCT() with a
double unary in front I again get #VALUE!

Is there any other way of adding them up?

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Bob Phillips]

Two choices Sandy.

Either force it to a number at the start

=--(MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},"")))))

or coerce in the sum

=SUM(--F1:F5)

as an array formula

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Sandy Mann]

Yes I tried both of them but as I said if there is a cell with just text and
no numbers the they both return #VALUE! But then perhaps the OP does not
have text only cells

Anyway, as you know it's late so I will sleepin it.

--
Good night,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Bob Phillips]

=SUM(IF(NOT(ISERROR(F1:F5)),F1:F5))

array formula

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Sandy Mann]

Thank you Bob - I knew you could do it <g>

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk