Subscribe Bookmark RSS Feed

Formula needed to remove non numeric from a numeric field

vinkane

Community Trekker

Joined:

Dec 23, 2013

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

2 REPLIES
mikedriscoll

Community Trekker

Joined:

Jun 23, 2011

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).

ms

Super User

Joined:

Jun 23, 2011

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