nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Find words within a column and relace it with with blank

chandankishor66

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,

Accepted answers

Jed_Campbell

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

All comments

jthi

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

Jed_Campbell

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

pauldeen

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.

hogi

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.