topic Find words within a column and relace it with with blank in Discussions

Find words within a column and relace it with with blank

chandankishor66 — Fri, 09 Jun 2023 16:02:29 GMT

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,

Re: Find words within a column and relace it with with blank

jthi — Mon, 14 Nov 2022 15:25:18 GMT

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

Re: Find words within a column and relace it with with blank

Jed_Campbell — Mon, 14 Nov 2022 15:25:51 GMT

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

Re: Find words within a column and relace it with with blank

pauldeen — Mon, 14 Nov 2022 15:28:39 GMT

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.

Re: Find words within a column and relace it with with blank

hogi — Tue, 15 Nov 2022 17:09:19 GMT

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.