Format a number with num() to use ' as separator in textbox

[peschu123]

Hi,

I use a Textbox to show a value.

The Value is something like "10123456" and I want it formatted like this: "T€ 10'123"

I played around with num() but the main problem is the ' (apostrophe) ...

I hope somebody can help me out.

Best Regards,

Peter

[rbecher · Accepted solution]

= num(round(sum({<[field1]={0}>} Costs) / 1000)), 'T€ #''##0', '.', chr(39))

[peschu123]

Works like a charm

Thank you Ralf and all other answers

If I get it right the sign between ### has to be the same like the thousand separator at the end?

I tried '#.##0',',',chr(39) and it seemed to simply ignore the chr(39) ...

Best Regards,

Peter

[MK_QSL]

Dear Ralf,

Can you explain how it works?

it's works like a charm!!!

[Anonymous]

just check below script use chr(146)

LOAD '"' &'T$'&' '& left(value,2)&' '&chr(146)&mid(value,3,3)  &'"'  as magic

FROM

C:\Users\Anant\Desktop\test.xlsx

(ooxml, embedded labels, table is Sheet7);

anant

[rbecher]

The second parameter is the format-code which is used:

num(expression [ , format-code [ , decimal-sep [ , thousands-sep ] ] ] )

3rd and 4th parameter are the separators in the given number

[rbecher]

..see above

[peschu123]

Hi Anant,

I couldn't try this yet, but I think this will result in a string instead a numeric value.

Which is not wanted in my case...