Problem with spaces

[Guest]

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

 

[Guest]

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.

 

[Guest]

First you have to convert text to value:

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

then continue with IF(... etc.

Regards,
Stefi


„nospaminlich†ezt írta:

 

[Ron Rosenfeld]

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

 

[Guest]

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

 

[Ron Rosenfeld]

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