change format of number

[RapplK]

Hello
Probably an easy question:

Can anyone help me to change the Format from a number as followed:

1000000,00 → 1.000.000,00

So basically just add the “.” between the thousands. Is there a special node?

Thank you a lot!
BR

[victor_palacios]

Have you tried Number Formatter – KNIME Community Hub

As well:

Convert a number with commas as thousands separators

Hi @mauuuuu5 By the way, the String To Number node allows you to specify Decimal separator and Thousand separator: [image] As the name says, it takes a String as input, so if you have an integer/double/long column, you can convert it to String, and then back to Number like this: [image] Input values (Table Creator): [image] Results: [image]

[bruno29a]

Hi @victor_palacios , probably the Number Formatter won’t do here, since the expected result is not a valid number “1.000.000,00”. This will end up as a String.

@RapplK this has to be done via a string manipulation. It can be done via a regexReplace() like this:
regexReplace("1000000,00", "[0-9](?=(?:[0-9]{3})+(?![0-9]))", "$0.")

Result:
1.000.000,00

You can do this via String Manipulation or Column Expression.

EDIT: @victor_palacios , sorry I thought the Number Formatter would produce a result of type number, but I checked and it will produce a string. But I don’t see any option of specifying a thousand separator.

[RapplK]

Hi @bruno29a
I did not express myselve clearly. I am very sorry for that.

I have several numbers in a column, and for all those numbers I would like to change the format

e.g. 1000000,00 → 1.000.000,00
-1342324,44 → -1.342.324,44
-188439,56 → -188.439,56
-273000 → -273.000 and so on…

Thank you again!
Regards

[duristef]

hi @RapplK,
I’ve found a regex that can probably help you (here). You can use it in the “String Manipulation” node, provided you’ve converted numbers to strings AND they’re rounded to max 3 decimal places

regexReplace($string$, "(?<=\\d)(?=(\\d\\d\\d)+(?!\\d))", ".")

[bruno29a]

Hi @RapplK , yes, I assumed that you were going to plug this on a column or columns. My example was just to show you how to use the function regexReplace() along with the expression.

So, you just need to plug your column instead of the string. In your case, it will be:
regexReplace($Amount$, "[0-9](?=(?:[0-9]{3})+(?![0-9]))", "$0.")