16 Replies Latest reply: Apr 22, 2015 8:07 AM by Ilonka Bachmann

# Format Number with apostrophe

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

• ###### Re: Format Number with apostrophe

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)),',','.')

• ###### Re: Format Number with apostrophe

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)

• ###### Re: Format Number with apostrophe

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

• ###### Re: Format Number with apostrophe

Hi Ilonka,

try with this little modified Ferico's solution:

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

Best regards

• ###### Re: Format Number with apostrophe

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

• ###### Re: Format Number with apostrophe

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

• ###### Re: Format Number with apostrophe

still get as result 999'9'999

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

• ###### Re: Format Number with apostrophe

Hi,

Regards

• ###### Re: Format Number with apostrophe

=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'))

• ###### Re: Format Number with apostrophe

I agree with Nick Hoff

=Num(999999999.99, '#''##0.00', '.', chr(39))    --> 999'999'999.99

Can you show use the exact expression you are using that is giving the wrong result. I don't think its system settings as the 2nd and 3rd parameters are overriding the system settings.

Note that the apostrophes in the format string is a pair of single apostrophes, not a double apostrophe.

HTH

Jonathan

• ###### Re: Format Number with apostrophe

Thanks for all the suggestions. Unfortunately none of them gives the correct result. (I used 2 single apostrophes, not double). The picture below shows the different results I've got.

What is interesting, if I format a number in the 'number' tab with 'money' I get the same result as with =Num(999999999.99, '#''##0.00', '.', chr(39)). Only when I remove one of the 2 single apostrophes in the format, I get the correct result. (which made me believe that some system settings may mess up the result). I'll go for the comma as thousands-sep. for the moment.

kind regards

• ###### Re: Format Number with apostrophe

Hi,

What is your

SET ThousandSep=',';

in script.

Regards

• ###### Re: Format Number with apostrophe

SET ThousandSep="'";   (double quote, single quote, double quote)

• ###### Re: Format Number with apostrophe

Hi,

Modify as i suggested in my earlier post then reload.

and then use

=replace(num(999999,'###,##0.00'),',',chr(39))

in

text object

Regards

• ###### Re: Format Number with apostrophe

wow! It's working now!

Many thanks and kind regards.