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
(1576 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

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

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