Formating numbers using Column Expressions

[dr_snglr]

Hello,

I’m trying to convert a double to a formated string, with a thousand separator.

1000.123 (double) to “1,000.1” (string), or “$1,000.1” (string)

I found good solutions using a Java snippet.

But is there a way how to do this using Column Expressions, or other out of the box nodes?

[elsamuel]

This is a complicated problem, and there isn’t a single KNIME node or Column Expressions function that will solve it.

This component is the next best thing.

If you want the output to be $1,000.1 then use the custom format ¤#,##0.0 (one decimal place)

If you want the output to be 1,000.1 then use the custom format #,##0.0 (one decimal place)

[bruno29a]

Hi @elsamuel , I don’t think @dr_snglr has any issues formatting the string, it’s successfully done via Java Snippet, but the specific request here is to do it via Knime nodes instead, including the Column Expressions.

If I am not mistaken, that component uses Java Snippet.

@dr_snglr , it can be done via Column Expressions using the regexReplace:
regexReplace(column("column1"), "[0-9](?=(?:[0-9]{3})+(?![0-9]))", "$0,")

And if you want to keep only 1 decimal place, you can use the toFixed() function, like this:
regexReplace(column("column1").toFixed(1), "[0-9](?=(?:[0-9]{3})+(?![0-9]))", "$0,")