Converting negative numbers
Posted by Jeanne on October 30, 2001 10:20 AM
I need to convert negative numbers that are imported into excel in this format 2- to -2. I cannot create a macro using replace because the number could be any value. Any suggestions?
Posted by Barrie Davidson on October 30, 2001 10:25 AM
You could put this formula in the adjacent column (assumes numbers in column A).
=IF(RIGHT(A1,1)="-",-VALUE(LEFT(A1,LEN(A1)-1)),A1)
Then select the column with the formula, copy, and PasteSpecial Values (over-writing the formula).
Does this help you out?
BarrieBarrie Davidson
Posted by Eric on October 30, 2001 10:26 AM
Use the following formula:
=IF(RIGHT(A1,1)="-",-1*SUBSTITUTE(A1,"-",""),A1)
Posted by Mark W. on October 30, 2001 1:17 PM
This one works better for {"2--","-2-","2-0-"}
=IF(LEN(A2)=FIND("-",A2),0-SUBSTITUTE(A2,"-",""),A2)
The conditional expression checks to see if there
is a single trailing minus and only a one minus.
