Export to Excel - Negative value in Parenthesis

Sbhat

I have a report that exports to excel. On the report i have a variable called 'NetLoss' which is of type Decimal and when there is a negative value then i want to show the value in parenthesis in Excel. For example NetLoss := -5000, i want to show it in excel as (5000) without the negative sign. No matter what i do when it goes to excel it just shows it as -5000, i tried '('+format(Netloss)+'), also i tried DELCHR function to remove the negative sign and no luck and then in MakeDataexcelbody i have

Addcolumn(Netloss,' ',true,true,false).

Does anyone know what can be done to make it work such that it shows (5000).

S.

winfy

format(-Netloss)

or

format(ABS(Netloss))

The first one: all values change the sign (vice versa)
The second one: always positive.

Decide yourself.

Chinmoy

Excel treats a column with () as a negative number so copy/paste this code:

TxtValue := '( ' + FORMAT(ABS(<NegativeDecimalValue>)) + ')';

and put the TxtValue in the cell. This will work.

Note: Please copy/paste the code because, the space here '( ' after the ( character is not a blank space.

Hope this helps.

Chn

Sbhat

Hi chinoy -

I copied and pasted your code and it shows in excel as -5000 and it you just put it as '('+format(Netloss)+') it shows it as (-5000) and shows the - sign.

Chinmoy

See the attached results it generates with me: (NAV 2009 R2)

ufuk

Another possibility is inserting #,##0 ;[Red](#,##0);- value to the NumberFormat field in Excel Buffer. (Remove [Red] part if you do not require) This way it will format value using Excel Format Cells property.

winfy

Oh, okay it is new to me!

NumberFormat 0,00;(0,00) works too!