how do I convert 256.60- to -256.60 ?
Posted by sid on September 11, 2001 1:32 PM
I am copying data from a company system that represents negative numbers with the negative sign following the number. When I save the data to a text file and open it up in a spreadsheet I need to convert the negative numbers to have the negative sign preceding the number without having to manually reenter the numbers. Any help would be greatly appreciated.
Posted by Aladin Akyurek on September 11, 2001 1:43 PM
One way woud be using a formula:
=IF(RIGHT(A1)="-",LEFT(A1,LEN(A1)-1)*-1,A1+0)
Aladin
Posted by DonC on September 12, 2001 5:55 AM
Aladin,
I understand the first part of the IF statement where you strip the "-" and multiply by -1 but I don't understand the second part of the IF statement. Why do you add the "+0" to A1 in that part? I've seen the addition of +0 to other formulas on this BB and it's time to find out why it's there.
Thanks.
Posted by Aladin Akyurek on September 12, 2001 7:35 AM
> I understand the first part of the IF statement where you strip the "-" and multiply by -1 but I don't understand the second part of the IF statement. Why do you add the "+0" to A1 in that part? I've seen the addition of +0 to other formulas on this BB and it's time to find out why it's there.
DonC,
Text format cell A1 and B1 and type 10 in A1 and 5 in B1. You'll see 10 and 5 are both left-aligned. (Click on the Align Right button: At visual inspection they look just numbers.)
Now,
in C1 enter: =SUM(A1:B1) [1]
in D1 enter: =A1+B1 [2]
in E1 enter: =SUMPRODUCT((A1:B1)+0) [3]
+ in [2] and +0 in [3] invokes Excel's so-called coercion feature. That is, Excel treats the values it is given as numbers then carries out the requested operation.
The formula that I proposed
=IF(RIGHT(A1)="-",LEFT(A1,LEN(A1)-1)*-1,A1+0)
assumes that the values with a "-" as last char all will be text-formatted. Multiplying these with -1 makes them all true numbers. I took the precaution that the values without a "-" might all also be text-formatted, so A1+0 coerces them to become true numbers in case they are not.
Aladin
Posted by Declan on September 14, 2001 8:51 AM
Why not simply reformat the number using Custom format
#,##0.00;#,##0.00-
