Format Number with apostrophe

[#1]

Hi all,

I would like to format a number in an expression. The format should look like this: 999'999.00

I've tried several things, but I cant get the result I whish.

if i use; =num(amount,'#''##0.00','.',chr(39))  the result looks like this: 99'9'999.00

when replacing the 2 apostrophe with a comma =num(amount,'#,##0.00','.',chr(39))  the result looks like this: 999999,# #0.00

Can someone tell me how to format this number correctly?

Thanks

Ilonka

[fvelascog72]

In Properties, Tab Number choose Fixed to 2 decimals and change Decimal Separator with . and Thousand Separator with '.

Or perhaps with this:

Replace(Replace(num(Number,'#.##0,00'),'.',chr(39)),',','.')

[#3]

Thanks for the suggestion.

In the Tab Number I cant see any dimension. (my number is a Dimension).

With Replace(Replace(num(Number,'#.##0,00'),'.',chr(39)),',','.') I get 99999999'900.00 (original number is 9'999'999.99)

[anlonghi2]

Hi Ilonka,

try with this little modified Ferico's solution:

replace(num(sum([Material Code]),'###,##0.00'),',',chr(39))

Best regards

[NickHoff]

= num(($vTestNum), '#''##0', '.', chr(39))

[anbu1984]

=Replace(num(9999999,'#,##0.00'),',',chr(39))

[NickHoff]

There isn't a need to use replace, my solution works just replace the variable with your field.

[#8]

still get as result 999'9'999

Is it possible that some system region settings are messing up the format?

[PrashantSangle]

Hi,

Post your sample apps.

Regards

[robert_mika]

Replace 999999 with your dimension

=TextBetween(num(999999,'#,##0.00'),'',',')&Chr(39)&mid(num(999999,'#,##0.00'),FindOneOf(num(999999,'#,##0.00'),',')+1,100)

You can replace the 100  with any number or len(num(999999,'#,##0.00'))