I need format currency 2 ways. If price is "integer" like "2000", output is "2 000 €". If price is like "20,5", output should be "20,50 €".
I use this:
IF (CONTAINS([C3],",50"), REPLACE([C3], ",50", ",50 €"),REPLACE([C3], ",00", " €")), but output is without "€", instead "," i have "." and space between thousands gone.
In [C3] may be all types.

I try to format all like i want in excel and copy it in notepad. Its worked, but i need to do this with very large databases repeatedly.

Thanks

Bea,

From what I understand, you can have either integer values like "2000", or a value with a comma like "20,5".

I've checked with the "Currency" type, but couldn't really get the desired output. So I came up with an expression that will do exactly what you want, no matter the numbers.


IF(CONTAINS([VALUE], ","), MID([VALUE], 1, POS([VALUE], ",", 1)) & RIGHT([VALUE], LEN([VALUE]) - POS([VALUE], ",", 1)) & IF(LEN(RIGHT([VALUE], LEN([VALUE]) - POS([VALUE], ",", 1))) = 1, "0", "") & " €", TRIM(RIGHT(MID([VALUE], 1, LEN([VALUE])-12), 3) & " " &RIGHT(MID([VALUE], 1, LEN([VALUE])-9), 3) & " " & RIGHT(MID([VALUE], 1, LEN([VALUE])-6), 3) & " " & RIGHT(MID([VALUE], 1, LEN([VALUE])-3), 3) & " " & RIGHT(MID([VALUE], 1, LEN([VALUE])), 3)) & " €")

NOTE: I've used [VALUE] for my own tests, but replace it by [C3] or whatever field name you need.

While not quite an "elegant" expression, it does exactly what it's supposed to do. It adds a space every three digits for integer numbers, and adds the zero after the comma after the comma-delimited values, as well as the "€" symbol. For the integers, I've made it so that it will handle up to 12 digits, but you can add more "RIGHT" commands if you need more than that somehow.

Hope that helps!

Regards,
Raphaël Lalonde Lefebvre

Thank you, its OK.