Prev: Pivot Table showing wrong data
Next: Calculating 6 days from a date
From: Pam on 3 Mar 2010 10:31
I've copied/pasted numbers from a screen to excel. The data represents hours
in xx.yy format. I am trying to subtract 2 cells, but get an error. I
determined that the cells have extra spaces so I used clean(). I still get
an error when subtracting. I tested the cell with isText(clean()) -- and it
indicates it is TRUE. So I tried Value(clean()) and Value gives #VALUE
error. Please help
From: Luke M on 3 Mar 2010 11:19
If you call up the XL help file on CLEAN, you'll see that it doesn't
actually remove all non-prinatable characters. The most common one from the
internet is CHAR(160). You might try:

=VALUE(SUBSTITUTE(A2,CHAR(160),""))

--
Best Regards,

Luke M
"Pam" <Pam(a)discussions.microsoft.com> wrote in message
news:555F7757-E3ED-499B-AEB5-200CC9078FA2(a)microsoft.com...
> I've copied/pasted numbers from a screen to excel. The data represents
> hours
> in xx.yy format. I am trying to subtract 2 cells, but get an error. I
> determined that the cells have extra spaces so I used clean(). I still
> get
> an error when subtracting. I tested the cell with isText(clean()) -- and
> it
> indicates it is TRUE. So I tried Value(clean()) and Value gives #VALUE
> error. Please help


From: Gary''s Student on 3 Mar 2010 11:35
Try:
=VALUE(SUBSTITUTE(CLEAN(E21),CHAR(160),""))

--
Gary''s Student - gsnu201001
 | 
Pages: 1
Prev: Pivot Table showing wrong data
Next: Calculating 6 days from a date