Find words within a column and relace it with with blank

[#1]

Hello JMP community,

I want to remove ppm. from column 9 and store only numbers in column 10.

Can you please suggest any way to perform this task?

Many thanks,

[#2]

Using formula with Substitute() is one option (replace "851 ppm." with :Column 9)

Substitute("851 ppm.", "ppm.", "");

 Edit:

Might have to add Trim Whitespace()/Num() around substitute

[#3 · Accepted solution]

Hi Chandan,

Assuming "ppm" is always the thing you want to remove, creating a new formula column to keep only the first word, while using "ppm" as a delimiter should work. Formula for this below:

Num(Word(1, :Column 9, "ppm"))

[#4]

Create a new column with the formula:

Num( Regex( :Column 9, "\d+" ) )

\d means find valid digits. + means as many as possible. The whole result is type cast back into a number by the num() around it.

[#5]

The regex version is nice.

If you don't want the rounded number:

Regex( :Column 9, "\d+,\d+" );

edited: as I just noticed that the numbers have comma as decimal separator
I think it's a good idea to have ponit as the standard in jmp and convert commas to points as soon as the file is loaded.