text to Column and amount from -ve to +ve.

[Guest]

I export data from other prog. To excel and converting to text to column. I
have fields like Date, Ref No., Item, Opening Bal, Pending Bal. In my
Opening Bal, Pending Bal. column some amount comes in – (-ve) and some comes
in + (+ve) I want to convert –ve amount into +ve and +ve into – ve .
Is possible through program. Any help.
Thanks
Shital

 

[paul.robinson]

Hi
no need for a program;
1. If these are positive and negative values in simply create 2 new
columns and in the first cell of the first column type the formula
= -"A2"

where I have assumed the first number in your two Opening Balance and
Pending Balance columns is in "A2". Now copy this formula to the first
cell in the second new column, then select all of the two columns and
do Edit, Fill Down.

2. If these are two columns of TEXT with "+" and "-" at the start of
each number do the same as 1 but use the formula
=IF(LEFT(A2,1) = "+",REPLACE(A2,1,1,"-"),IF(LEFT(A2,1) =
"-",REPLACE(A2,1,1,"+")))


regards
Paul

 

[Guest]

thanks Paul for reply this will work but

There are only (-)negative sign in amount and not (+)postive sign. I want to
canvert +amount into -nagative amount 1st and than i don't want that -ve
amount +ve.
And remaining -ve amount into +ve.

Thanks

 

[paul.robinson]

Hi
Won't option 1 do this?

Paul