I'm having trouble formatting a decimal number to be displayed with a thousand separator and a decimal separator if needed. Number can have up to three decimal digits. I have a feeling that I'm missing something very obvious here.
Basically:
1 -> 1
1.11 -> 1.11
1.111 -> 1.111
I have been using codes for formatting number and so far I've tried this combinations:
\# ,0
\# ,0.000
\# ,#
\# ,#.###
\# #,#
\# #,#.###
Basically, for a value of 1.11 I've gotten either a 1 as a result or 1.110 as a result.
Given your most recent comment (i.e. "The database returns a padded number 1.110 for example"), your data don't have 'up to three decimal digits'; they have exactly three decimal digits. If this is for a mailmerge, you could obtain both the thousands separator and suppression of trailing 0s with a field coded as:
{={MERGEFIELD MyField} # ,0.###}
Where the number ends with a trailing decimal 0, you'll end up with a space where the 0 would otherwise be and, where it's an integer, you'll end up with a trailing decimal point. If you really want to omit all this trailing stuff, you should modify the data so it's stored in the appropriate format; otherwise some fairly complex field coding will be required to achieve your desired outcome:
{QUOTE{SET Val {MERGEFIELD MyField}}{IF{=INT(Val) # 0.000}= {REF Val} {=INT(Val) # ,0} {IF{=INT(Val*10)/10 # 0.000}= {REF Val} {=Val # ,0.0} {IF{=INT(Val*100)/100 # 0.000}= {REF Val} {=Val # ,0.00} {=Val # ,0.000}}}}}
Note: The field brace pairs (i.e. '{ }') for the above examples are all created in the document itself, via Ctrl-F9 (Cmd-F9 on a Mac); you can't simply type them or copy & paste them from this message. Nor is it practical to add them via any of the standard Word dialogues. The spaces represented in the field constructions are all required.