community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Knowledge Base

Definitive answers from Designer experts.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
Announcement | We'll be doing maintenance between 2pm - 5pm MT, which may impact your experience. Thanks for your patience as we work on improving the community!

Converting strings to numbers

Alteryx
Alteryx
Created on

Sometimes, a dataset will contain numbers stored as text. I order to do calculations using those numbers, the datatype will need to be converrted to a numeric data type. 

 

If the data is clean, changing the data type in a select tool can do the trick. 

 

Another option is to use the TONUMBER() function in a formula tool or multi field formula tool (if you have more than one field to convert). 

 

TONUMBER(x, bIgnoreErrors, keepNulls)

 

x: the column you are converting to a number

bIgnoreErrors and keelNulls are optional parameters. 

bIgnoreErrors: 0 or false (default) means it will report conversion error messages; 1 or true means it will ignore conversion errors.
keepNulls: 0 or false (default) means it will convert non-numeric values (including null) to zero; 1 or true means it will convert non-numeric values to null.

This option can be very helpful if you want to treat nulls differently than zero in your dataset. 

 

 

Now, what if your data is messy? What if users added leading zeros, currency symbols, etc? 

You can use the formula tool to clean up your data before converting it to numbers. 

 

Functions you can use to clean data: 

REPLACECHAR(x, y, z): Returns the string [x] after replacing each occurrence of the character [y] with the character [z].

 

REGEX_REPLACE(string, pattern, replace,icase): Allows replacement of text using regular expressions and returns the string resulting from the RegEx find pattern and replace string.Consult the Boost Regex Perl Regular Expression Syntax page to make the building of the expression easier.The replace parameter can be either a specified value as shown below, or a marked group, such as "$1"
The icase is an optional parameter. When specified, the case must match. By default icase=1 meaning ignore case. If set to 0, the case must match.

 

TRIM(x, y): Remove the character(s) in the string y from the ends of the string x. Y is optional and defaults to trimming white space. Notice in the TRIM function examples the specified characters are trimmed. It doesn't matter what order the characters are in.

 

 

 Also see the attached workflow for examples of how to use the Formula tool to clean data before converting it to a number. 

 

Attachments
Comments

For me the conversion for string to number or number to string did not work ...SELECT command came in handy to change the way I want input to be 

Meteor

Hello all,

Regarding data conversion from string to Int.

My data set contains numbers stored as text. 

When I try to convert either with Select or Formula tool Alteryx treats the first comma as decimal number

Before conversion 31,396,212.06 (V_WString)

After Conversion 31 (Double, Int and all other numeric options)

I'll appreciate your help.

Thanks in advance.

 

Alteryx Certified Partner

@ilotvin

This would be considered "messy data", so use the function mentioned by @HenrietteH above...

REPLACECHAR(your_field, ",", "")

...to get rid of the commas first.

Then you can convert to a number.

Meteor
hey, I am wondering what the best way to code in a letter-number cipher. (i.e. A=1, B=2, C=3,...) My data currently exists as an 8 character string, with 7 digits, and the last value being a number. This string is supposed to represent an 8 digit number, where the last digit is converted to a letter to mark the end of the column (from a very old data set in a flat file). Any advice? tia