Exclude text from sums
 

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

Exclude text from sums
 

Use SUM which ignores non-numeric data

"Be18" wrote:

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

Exclude text from sums
 

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" wrote:

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

Exclude text from sums
 

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

"Toppers" wrote:

Use SUM which ignores non-numeric data

"Be18" wrote:

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

Exclude text from sums
 

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

--
HTH

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


with @tiscali.co.uk


"Be18" wrote in message
...
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!!

Exclude text from sums
 

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

"Sandy Mann" wrote:

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

--
HTH

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


with @tiscali.co.uk


"Be18" wrote in message
...
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!!

Exclude text from sums
 

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&"01234 56789")),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)



"Be18" wrote in message
...
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

"Toppers" wrote:

Use SUM which ignores non-numeric data

"Be18" wrote:

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

Exclude text from sums
 

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...
--
*****
birds of the same feather flock together..



"Toppers" wrote:

Use SUM which ignores non-numeric data

"Be18" wrote:

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

Exclude text from sums
 

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


with @tiscali.co.uk


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

"Sandy Mann" wrote:

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

--
HTH

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


with @tiscali.co.uk


"Be18" wrote in message
...
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!!

Exclude text from sums
 

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


with @tiscali.co.uk


"Bob Phillips" wrote in message
...
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&"01234 56789")),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)



"Be18" wrote in message
...
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

"Toppers" wrote:

Use SUM which ignores non-numeric data

"Be18" wrote:

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

Exclude text from sums
 

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&"01234 56789")),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" wrote in message
...
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


with @tiscali.co.uk


"Bob Phillips" wrote in message
...
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&"01234 56789")),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)



"Be18" wrote in message
...
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

"Toppers" wrote:

Use SUM which ignores non-numeric data

"Be18" wrote:

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

Exclude text from sums
 

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


with @tiscali.co.uk


"Bob Phillips" wrote in message
...
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&"01234 56789")),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" wrote in message
...
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


with @tiscali.co.uk


"Bob Phillips" wrote in message
...
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&"01234 56789")),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)



"Be18" wrote in message
...
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

"Toppers" wrote:

Use SUM which ignores non-numeric data

"Be18" wrote:

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

Exclude text from sums
 

=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" wrote in message
...
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


with @tiscali.co.uk


"Bob Phillips" wrote in message
...
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&"01234 56789")),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" wrote in message
...
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


with @tiscali.co.uk


"Bob Phillips" wrote in message
...
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&"01234 56789")),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)



"Be18" wrote in message
...
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

"Toppers" wrote:

Use SUM which ignores non-numeric data

"Be18" wrote:

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

Exclude text from sums
 

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


with @tiscali.co.uk


"Bob Phillips" wrote in message
...
=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" wrote in message
...
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


with @tiscali.co.uk


"Bob Phillips" wrote in message
...
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&"01234 56789")),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" wrote in message
...
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


with @tiscali.co.uk


"Bob Phillips" wrote in message
...
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&"01234 56789")),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)



"Be18" wrote in message
...
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

"Toppers" wrote:

Use SUM which ignores non-numeric data

"Be18" wrote:

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