turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Discussions
- :
- Formula needed to remove non numeric from a numeric field

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jun 8, 2016 11:37 AM
(1473 views)

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

2 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jun 8, 2016 2:04 PM
(1302 views)

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jun 8, 2016 3:24 PM
(1302 views)

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