From String to Number

[Cfdiaz2103]

Hi Everyone!

I would like to know if there's any way to convert a string data to a number, with all thousand and decimal separators

Example:

Before

1,000,500.23   (String)

After

1,000,500.23  (Number)

The main idea is summarizing some fields in excel from the output in order to validate totals, due to string data can only be counted

Thanks for your response.

[apathetichell]

tonumber() works fine in a formula or multi-field. just make sure you aren't converting to integer.

[HomesickSurfer]

Hi @Cfdiaz2103 

If you don't need the commas, and keeping the decimals, try replacing the commas with "" and then use the Select Tool to change the type and size to Fixed Decimal, 19.2.

How this works for you...

[HomesickSurfer]

Hi @apathetichell 

ToNumber() didn't work for me.  It remains as a String type and 1,000,500.23 results in a value of 1

[apathetichell]

your problem is probably that you are keeping it in the same field and the field is set as string.

go into formula create a new field - like "stringtonumber"

tonumber([field1 - or whatever your field is called]

This version keeps the field names the same.

[apathetichell]

also - could you have comma set as your decimal separator in localization/user settings?

[HomesickSurfer]

Hi @apathetichell 

I ran your workflow and receive the following conversion error, resulting in a value of 1.

ConvError: Multi-Field Formula (2): TONUMBER: 1,23423,23.23 stopped converting at a comma. It might be invalid.

I don't see "decimal separator in localization/user settings"

[apathetichell]

options/user settings/edit user settings:

decimal separator is an option under user settings/localization  which allows a user to  use the "," like one would in some parts of the world - as a decimal separator. If that isn't the issue - could you post more  of your workflow and more sample data?

Honestly I'm bewildered by this as the official description of tonumber() shows it converting strings with commas as has been my experience.

https://help.alteryx.com/current/designer/conversion-functions

It's also possible that your "," is not a standard "," but a different unicode character that looks like a "," b the machine is not reading as such. If that's the case you can use an alternative formula to get the number convert to work... try this:

tonumber(replace([_currentfield_],",","")) and see if it helps at all.

[HomesickSurfer]

Hi @apathetichell 

Thx.  Tried the tonumber(replace([_currentfield_],",",""))...still results in a string, with conversion error "ConvError: Multi-Field Formula (2): TONUMBER: 1,23423,23.23 stopped converting at a comma. It might be invalid.", however, appearing correctly without commas.

Used your workflow and got a '1' as Double, or '1.00' as Fixed Decimal 19.2.

And...no option in settings/localization set a decimal separator.

[apathetichell]

O.k. - try this.

drag a regex tool and set to regex replace.

use this in the tool:

([0-9\.]+)

put $1 in the replacement text box and unclick "copy unmatched text to output"

If this doesn't work could you post a few examples of your numbers? There's a few other ways that you can go about it (reverse the strings and loop off the decimals, datacleanse the whole numbers of all punctuation and return the decimals and convert comes to mind.)

Heres what you would use for separating out the decimals - reversestring(REGEX_Replace(reversestring([numberworkaround]),"(\d+[\.]).*",
"$1"))

then you'd use that new column in a replace on your old column to isolate and then run it through datacleanse with remove all punctuation selected.