Convert a number with commas as thousands separators

Hi everyone, I am trying to convert a number with commas as thousands separators, so I guess the number shall be converted to a string and then try to apply the format.

I have found some JavaScript examples online, but when applying the solution it just converts the number to a string without the separators.

Any help will be appreciated

Best Regards

Mau

Hi @mauuuuu5, does the following link give you the help you need, or at least give you some pointers? It uses java rather than javascript…

1 Like

Hi @takbb thank you for the link, I have not seen that post. I downloaded the workflow and tried and it partially works, as you have to manually change the column name inside the Java Snippet node and not in the component despite that it has a dropdown list to “select” the column.

image

In summary the solution is nice but in my case I need to apply it in several columns.

Thank you

Mau

1 Like

Hi everyone I found a workflow that adds the commas to a string

Based on the previous solution I did a slight modification in case your input column is a double type column

join("$",column("VAL_A").toLocaleString('fullwide', {useGrouping:false}).replace(/(\d)(?=(\d\d\d)+(?!\d))/g, "$1,"));

Column Expression Question.knwf (11.1 KB)

Thank you

Hi @mauuuuu5 By the way, the String To Number node allows you to specify Decimal separator and Thousand separator:

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

3 Likes

Hello @mauuuuu5,

workflow you found is from this discussion:

But have tried Component from topic linked by @takbb and seems it works as expected although my column column is configured as input. Probably worth upgrading Component regarding this and adding multiple column support…

Br,
Ivan

Hi @bruno29a , I liked your answer but it had me intrigued as I couldn’t see how a numeric could remain as a Double and yet contain commas… following String to Number. So I had to investigate further.

So when I tried it out, firstly telling it to use “,” as thousands separator, sure enough I got this…
image

so… still puzzled, I tried using “x” as a thousands separator…
image
and I still got this
image

And then I realised that the “thousands separator” on that screen indicates the thousands separator on the incoming string (if there are any) is, rather than on the outgoing number, and that the commas appearing on the output for that node are just (coincidentally) from the renderer and haven’t been included in the number after all. Sadly, if you try to do anything with the number, such as String manipulation
image

you’ll find that under the hood its still the same old double value without the formatting as it isn’t really there :cry:
image

sorry to be bearer of bad news…

Hi Ivan thank you, I will check it out

1 Like

Hey @takbb , as usual, thanks for the input.

Indeed, the options look to be characters to look for on the incoming string, and it kinda what the option says (which I missed):

It looks like the String to Number would add the thousand separator regardless.

In relation to the separators not being included in the number, it’s definitely something that I would want, as the separators should be for display only - it all depends on what want to achieve in the end, and this is a personal preference.

My reason being, I don’t want to lose the real value of these records, in case I need to do operations on them, and you can’t process them if they physically have these separators in them. With the separators physically in them, they’re basically strings - think of storing these values in a db table, you would want to keep them as numeric in the db, and render the separator only for display.

Again, it depends on what you want to achieve :slight_smile:

1 Like

Hi. I have developed a component that converts selected numeric columns to String by specifying a locale. The locale controls the characters used for things like thousands separator, decimal separator, etc. Additionally, a decimal format pattern can be used for finer control of the output format (number of decimal places, whether to use scientific notation, currency formats, etc.).

I have supplied a number of predefined formats. Please give it a try and provide feedback if you are so inclined. Any suggestions for improvement appreciated.
Thanks,
Don

Here’s the dialog of the component:

Number To String (Locale).knwf (39.0 KB)

2 Likes

Hi @dnaki I will try the component thank you very much

Nice one @dnaki :+1:
Ivan