One way woud be using a formula:
=IF(RIGHT(A1)="-",LEFT(A1,LEN(A1)-1)*-1,A1+0)
Aladin
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.
> 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
Why not simply reformat the number using Custom format
#,##0.00;#,##0.00-