Problem with spaces
 

I have the following data in C1 to D6
Paid Received
£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(C2<"",-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(C2<"",-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 lot

Problem with spaces
 

First you have to convert text to value:

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

then continue with IF(... etc.

Regards,
Stefi


€žnospaminlich€� ezt Ã*rta:

I have the following data in C1 to D6
Paid Received
£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(C2<"",-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(C2<"",-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 lot

Problem with spaces
 

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

TEXT({Value}, {Format})

So if you try using this it may work.

if(C2<"",TEXT(C2, "$#,##0.00")*-1,TEXT(C2, "$#,##0.00"))

HTH.

"nospaminlich" wrote:

I have the following data in C1 to D6
Paid Received
£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(C2<"",-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(C2<"",-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 lot

Problem with spaces
 

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

I have the following data in C1 to D6
Paid Received
£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(C2<"",-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(C2<"",-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 lot

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

Try the following:

=TRIM(SUBSTITUTE(C2,CHAR(160),""))

in place of just TRIM(C2)





--ron

Problem with spaces
 

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

=VALUE(IF(C2<"",-TRIM(SUBSTITUTE(C2,CHAR(160),"")),TRIM(SUBSTITUTE( D2,CHAR(160),""))))

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

Thanks again

Problem with spaces
 

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

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

=VALUE(IF(C2<"",-TRIM(SUBSTITUTE(C2,CHAR(160),"")),TRIM(SUBSTITUTE( D2,CHAR(160),""))))

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

Thanks again

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