topic Formula needed to remove non numeric from a numeric field in Discussions

Formula needed to remove non numeric from a numeric field

vinkane — Wed, 08 Jun 2016 18:37:41 GMT

Is there a formula to remove a non numeric from a numeric data field ?

Re: Formula needed to remove non numeric from a numeric field

mikedriscoll — Wed, 08 Jun 2016 21:04:23 GMT

Assuming your existing column is character data type, and your new column with a formula is numeric, you can set the column formula for the new numeric column to be: try(num(:col)) where col is the column name. This seems to work without the try statement, so I suppose you can just use num(:col).

Re: Formula needed to remove non numeric from a numeric field

ms — Wed, 08 Jun 2016 22:24:27 GMT

See examples below. The first removes everything but integers. The second allows for decimal numbers (assuming period as delimiter).

Formatted numerics such as dates or scientific notation require a little more complex search string.

pi=":3b@./14Q15%9";

num(Regex(pi, "[^0-9]+", "", Globalreplace));

num(Regex(pi, "[^0-9.]+", "", Globalreplace));

Edit: just learned this has posted before. See Craige's excellent post in this thread Extract numbers from string in JSL