Better expression than SUBSTITUTE to deal with thousands separator in template?

[Gonzague_Issen1]

Dear all, 

here is an interesting challenge. I am trying to get a decimal number as a text format in an xml file template without any thousands separator and a period as the decimal separator: xxxx.xx
In my locale, decimals appear with a space for thousands separator and a comma for decimal separator so here's what I get when I simply use <<[Amount]>> :  x xxx,xx
So following the advice from esteemed members of this community, I used this expression to replace both the space and the comma:

<<SUBSTITUTE(SUBSTITUTE(TEXT([Amount]),",",".")," ","")>>

However, it doesn't work: I get x.xxx.xx and I don't know why this first period appears there. I suspect that the thousand separator is not a true "space" but maybe a "no break space", which I can't copy in the expression.

So I tried something else: 

<<FLOOR([Amount])&"."&RIGHT(TEXT([Amount]-FLOOR([Amount])),2)>>

It works, but it is rather convoluted. Especially now that I need to replace [Amount] by a SUM(), which will need to be calculated three times!

Any suggestions ?
Thanks

G

[Denny774]

Hey man,

here you go:

TEXT(TRIM(LEFT([Amount], LEN(TEXT([Amount])) - 1)))

[Gonzague_Issen1 · Accepted solution]

Thanks Denny.

Actually I don't need to eliminate the character to the right with your suggested LEFT([Amount], LEN(TEXT([Amount])) - 1) expression because I don't have any monetary symbol to the right. 

But I tried the TRIM() function which you suggested and I didn't know, by first simply testing:

<<TRIM([Amount])>>

and was amazed: not only did it eliminate the space but it also converted the comma to a period!

So bull's eye!

Thanks man!