This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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.