cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Submit your abstract to the call for content for Discovery Summit Americas by April 23. Selected abstracts will be presented at Discovery Summit, Oct. 21- 24.
Discovery is online this week, April 16 and 18. Join us for these exciting interactive sessions.
Choose Language Hide Translation Bar
vinkane
Level III

Formula needed to remove non numeric from a numeric field

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

2 REPLIES 2
mikedriscoll
Level VI

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

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 (Alumni) ms
Super User (Alumni)

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

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