Ive been trying different ways all day to get this to work..thats how i
found this thread..nothing ive tried off of here so far has worked and
im sure its simple and the answer is here somewhere but my eyes are
starting to go the shape of my monitor..

The problem is...
i recieve data in email ...copy n paste to Excel ...try to SUM but
blahhh..

Snapshot of part of a column im trying to SUM

1.32 DR
1.34 DR
1.42 DR

0

i got the following formula off of one thread and it worked with data i
typed into cells manually but did not work with the data pasted from
email

=SUM(IF(ISNUMBER(--SUBSTITUTE(UPPER(A1:C1925),"V","")),
--SUBSTITUTE(UPPER(A1:C1925),"V","")))

ive tried copying clear cell.. special paste ..values /add...still no
good....

where to next???

Any thoughts greatly appreciated.

Regards Andy

=SUM(IF(ISNUMBER(--LEFT(A1:A10,FIND(" ",A1:A10)-1)),--LEFT(A1:A10,FIND("
",A1:A10)-1)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Andy" wrote in message
oups.com...
Ive been trying different ways all day to get this to work..thats how i
found this thread..nothing ive tried off of here so far has worked and
im sure its simple and the answer is here somewhere but my eyes are
starting to go the shape of my monitor..

The problem is...
i recieve data in email ...copy n paste to Excel ...try to SUM but
blahhh..

Snapshot of part of a column im trying to SUM

1.32 DR
1.34 DR
1.42 DR

0

i got the following formula off of one thread and it worked with data i
typed into cells manually but did not work with the data pasted from
email

=SUM(IF(ISNUMBER(--SUBSTITUTE(UPPER(A1:C1925),"V","")),
--SUBSTITUTE(UPPER(A1:C1925),"V","")))

ive tried copying clear cell.. special paste ..values /add...still no
good....

where to next???

Any thoughts greatly appreciated.

Regards Andy

Thanks Heaps Bob for your prompt reply ..but unfortunatly it didnt work

Regards Andy

Hi Andy

With the data selected, try:

Edit | Replace | What: DR | With: (nothing)


---
Regards,
Norman



"Andy" wrote in message
oups.com...
Ive been trying different ways all day to get this to work..thats how i
found this thread..nothing ive tried off of here so far has worked and
im sure its simple and the answer is here somewhere but my eyes are
starting to go the shape of my monitor..

The problem is...
i recieve data in email ...copy n paste to Excel ...try to SUM but
blahhh..

Snapshot of part of a column im trying to SUM

1.32 DR
1.34 DR
1.42 DR

0

i got the following formula off of one thread and it worked with data i
typed into cells manually but did not work with the data pasted from
email

=SUM(IF(ISNUMBER(--SUBSTITUTE(UPPER(A1:C1925),"V","")),
--SUBSTITUTE(UPPER(A1:C1925),"V","")))

ive tried copying clear cell.. special paste ..values /add...still no
good....

where to next???

Any thoughts greatly appreciated.

Regards Andy

Hi & Thanks Norman...

The DR disappears but it still wont SUM

Regards Andy

Norman!!!!
Close...how can i delete the space between the numbers and the Text?

Andy

Hi Andy

the following array formula works for me
(Array formulae must be entered or edited using Ctrl+Shift+Enter not
just Enter. Excel will insert the curly braces { }. Do not type them
yourself)
{=SUM(--SUBSTITUTE(A1:A3," DR",""))}
Note the space in front of DR within the quotes.

It works with or without the space for me in XL2003, but you may need
the space substituted as well in your version.

--
Regards

Roger Govier


"Andy" wrote in message
oups.com...
Norman!!!!
Close...how can i delete the space between the numbers and the Text?

Andy

Hi Roger,

Tried it =SUM(--SUBSTITUTE(A1:A3," DR","")) but nope... got
#value!

tried to modify what you said to...
=SUM(SUBSTITUTE(H22:H24,H22:H24,"")) and got a 0 . I think the
problem is that space between the numbers and text

Thanks anyhow
Andy

On 29 Jan 2006 03:59:43 -0800, "Andy" wrote:

Ive been trying different ways all day to get this to work..thats how i
found this thread..nothing ive tried off of here so far has worked and
im sure its simple and the answer is here somewhere but my eyes are
starting to go the shape of my monitor..

The problem is...
i recieve data in email ...copy n paste to Excel ...try to SUM but
blahhh..

Snapshot of part of a column im trying to SUM

1.32 DR
1.34 DR
1.42 DR

0


Try this:

This assumes your format is as above -- a number followed by <space and then
non-numeric characters:

The **array formula**

=SUM(IF(ISERR(-LEFT(rng,FIND(" ",A1)-1)),0,--LEFT(rng,FIND(" ",A1)-1)))

To enter an array formula, hold down <ctrl<shift while hitting <enter.
Excel will place braces {...} around the formula.

If your formats can be more complicated, other solutions are available.

If the above doesn't work on the data from your email that you have pasted into
Excel, then there is likely some non-printing characters within the string. If
that is the case, you could either figure out exactly what they are by using
the CODE function, or you could download and install Longre's free morefunc.xll
add-in from http://xcell05.free.fr/

and use this regular expression **array** formula:

=SUM(IF(ISERR(-REGEX.MID(rng,"\d*\.?\d+")),
0,--REGEX.MID(rng,"\d*\.?\d+")))

This should pull out the first number in the string, and ignore non-printing
characters.


--ron

Thanks Ron,
Assuming that my data is in cells H22:H24 i would enter as follows....

=SUM(IF(ISERR(-LEFT(rng,FIND(" ",A1)-1)),0,--LEFT(rng,FIND(" ",A1)-1)))
becomes
=SUM(IF(ISERR(-LEFT(H22:H24,FIND(" ",A1)-1)),0,--LEFT(H22:H24,FIND("
",A1)-1)))

Is this correct?... if so no good ...and i must try the other option.

Regards Andy

On 29 Jan 2006 06:16:40 -0800, "Andy" wrote:

Thanks Ron,
Assuming that my data is in cells H22:H24 i would enter as follows....

=SUM(IF(ISERR(-LEFT(rng,FIND(" ",A1)-1)),0,--LEFT(rng,FIND(" ",A1)-1)))
becomes
=SUM(IF(ISERR(-LEFT(H22:H24,FIND(" ",A1)-1)),0,--LEFT(H22:H24,FIND("
",A1)-1)))

Is this correct?... if so no good ...and i must try the other option.

Regards Andy

What you did with the first formula is correct: Substituting H22:H24 for rng.

An alternate option would be to NAME (Insert/Name/Define) the range H22:H24 as
rng.

What kind of result do you get?

My suspicion, as I mentioned, is that either you have a non-printing character
in the string, or perhaps what looks like a <space is really something
different.

But the regular expression should sort that out as it is set up to only pick
out digits and the optional dot.
--ron

Hi Ron,
Thanks again,
....i got a 0 when i tried the last one....

But Bob Phillips has solved it for me with this...

=SUM(IF(ISNUMBER(--LEFT(B1:B10,FIND(CHAR(160),B1:B10)-1)),
--LEFT(B1:B10,FIND(CHAR(160),B1:B10)-1)))

Thanks heaps for all your help
Regards Andy

On 29 Jan 2006 15:03:20 -0800, "Andy" wrote:

Hi Ron,
Thanks again,
...i got a 0 when i tried the last one....

But Bob Phillips has solved it for me with this...

=SUM(IF(ISNUMBER(--LEFT(B1:B10,FIND(CHAR(160),B1:B10)-1)),
--LEFT(B1:B10,FIND(CHAR(160),B1:B10)-1)))

Thanks heaps for all your help
Regards Andy

I'm glad you got it working, although the one I supplied, if properly
implemented, will work whether the space is a normal space, or a no-break space
(char(160))


--ron