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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

shital shah - 23 Jan 2006 10:05 GMT

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

Reply to this Message

paul.robinson@it-tallaght.ie - 23 Jan 2006 10:36 GMT

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

Reply to this Message

shital shah - 23 Jan 2006 11:59 GMT

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

> Hi
> no need for a program;
[quoted text clipped - 14 lines]
> regards
> Paul

Reply to this Message

paul.robinson@it-tallaght.ie - 23 Jan 2006 14:16 GMT

Hi
Won't option 1 do this?

Paul

Reply to this Message