software

I have the following data in C1 to D6
PaidReceived
£90.00
£598.68
£4.38
£50.00
£66.12

The amounts are in text format with spaces after the last digit.

In Column E I'm trying to create a formula like =if(C2lt;gt;quot;quot;,-C2,D2) so I get
a single column of figures where amounts paid show as negative.

I've tried using Trim but it doesn't remove the space after the amount so
even with a formula like =if(C2lt;gt;quot;quot;,-Value(Trim(C2)),Value(Trim(D2)) I get an
error as it won't convert the trailing space to a value.

I'm afraid I'm stuck. How do I remove the spaces and get a value?

Can anybody give me a steer on this please. All help much appreciated.

Thanks a lotFirst you have to convert text to value:

=VALUE(MID(D2,2,255))

then continue with IF(... etc.

Regards,
Stefi„nospaminlich” ezt �*rta:

gt; I have the following data in C1 to D6
gt; PaidReceived
gt; £90.00
gt; £598.68
gt; £4.38
gt; £50.00
gt; £66.12
gt;
gt; The amounts are in text format with spaces after the last digit.
gt;
gt; In Column E I'm trying to create a formula like =if(C2lt;gt;quot;quot;,-C2,D2) so I get
gt; a single column of figures where amounts paid show as negative.
gt;
gt; I've tried using Trim but it doesn't remove the space after the amount so
gt; even with a formula like =if(C2lt;gt;quot;quot;,-Value(Trim(C2)),Value(Trim(D2)) I get an
gt; error as it won't convert the trailing space to a value.
gt;
gt; I'm afraid I'm stuck. How do I remove the spaces and get a value?
gt;
gt; Can anybody give me a steer on this please. All help much appreciated.
gt;
gt; Thanks a lot
gt;

You could try using the function TEXT. Arguements for text are ...

TEXT({Value}, {Format})

So if you try using this it may work.

if(C2lt;gt;quot;quot;,TEXT(C2, quot;$#,##0.00quot;)*-1,TEXT(C2, quot;$#,##0.00quot;))

HTH.

quot;nospaminlichquot; wrote:

gt; I have the following data in C1 to D6
gt; PaidReceived
gt; £90.00
gt; £598.68
gt; £4.38
gt; £50.00
gt; £66.12
gt;
gt; The amounts are in text format with spaces after the last digit.
gt;
gt; In Column E I'm trying to create a formula like =if(C2lt;gt;quot;quot;,-C2,D2) so I get
gt; a single column of figures where amounts paid show as negative.
gt;
gt; I've tried using Trim but it doesn't remove the space after the amount so
gt; even with a formula like =if(C2lt;gt;quot;quot;,-Value(Trim(C2)),Value(Trim(D2)) I get an
gt; error as it won't convert the trailing space to a value.
gt;
gt; I'm afraid I'm stuck. How do I remove the spaces and get a value?
gt;
gt; Can anybody give me a steer on this please. All help much appreciated.
gt;
gt; Thanks a lot
gt;

On Wed, 8 Feb 2006 03:23:27 -0800, nospaminlich
gt; wrote:

gt;I have the following data in C1 to D6
gt;PaidReceived
gt;�90.00
gt;�598.68
gt;�4.38
gt;�50.00
gt;�66.12
gt;
gt;The amounts are in text format with spaces after the last digit.
gt;
gt;In Column E I'm trying to create a formula like =if(C2lt;gt;quot;quot;,-C2,D2) so I get
gt;a single column of figures where amounts paid show as negative.
gt;
gt;I've tried using Trim but it doesn't remove the space after the amount so
gt;even with a formula like =if(C2lt;gt;quot;quot;,-Value(Trim(C2)),Value(Trim(D2)) I get an
gt;error as it won't convert the trailing space to a value.
gt;
gt;I'm afraid I'm stuck. How do I remove the spaces and get a value?
gt;
gt;Can anybody give me a steer on this please. All help much appreciated.
gt;
gt;Thanks a lot

The trailing space may be a no-break space (ASCII code 160).

Try the following:

=TRIM(SUBSTITUTE(C2,CHAR(160),quot;quot;))

in place of just TRIM(C2)--ron

Thanks for all the help from which I've come up with this formula:

=VALUE(IF(C2lt;gt;quot;quot;,-TRIM(SUBSTITUTE(C2,CHAR(160),quot;quot;)),TRIM(SUBSTITUTE( D2,CHAR(160),quot;quot;))))

......which seems to do the trick.

Thanks again

On Wed, 8 Feb 2006 05:48:15 -0800, nospaminlich
gt; wrote:

gt;Thanks for all the help from which I've come up with this formula:
gt;
gt;=VALUE(IF(C2lt;gt;quot;quot;,-TRIM(SUBSTITUTE(C2,CHAR(160),quot;quot;)),TRIM(SUBSTITUTE( D2,CHAR(160),quot;quot;))))
gt;
gt;.....which seems to do the trick.
gt;
gt;Thanks again

Good. Glad to help. Thanks for the feedback.
--ron