topic script to find and replace portions of character entries in a column in Discussions

script to find and replace portions of character entries in a column

fbm73 — Fri, 15 May 2020 14:48:02 GMT

In my data table, I have a column of data type Character with a column name of Size. The entries for the different rows in column Size are like 8 or so different variations of things like sglLrg500, sglLrg800, sglLrg150, etc. I would like to have a JMP script that can quickly replace sgl in each of the entries in Size with Sgl instead, so that sglLrg500 for example would become SglLrg500.

I could do this manually by selecting the Size column then selecting Edit, Search, Find, and then set Find What to sgl and Replace With to Sgl and check the box for "Restrict to selected columns" and the click Replace All. But I would like if possible a simple JMP script to do the same thing. Thanks.

Re: script to find and replace portions of character entries in a column

Dan_Obermiller — Fri, 15 May 2020 15:01:25 GMT

Why a script? Have you looked at Cols > Recode?

Re: script to find and replace portions of character entries in a column

ih — Fri, 15 May 2020 19:10:55 GMT

One option is to use regex, it has a pretty steep learning curve but is very powerful:

Regex( :Original Column, "sgl", "Sgl", IGNORECASE, GLOBALREPLACE )

Here is an example table:

New Table( "Replace Example",
	Add Rows( 3 ),
	New Column( "Original", Character, "Nominal", Set Values( {"sglLrg500", "sglLrg800", "sglLrg150"} ) ),
	New Column( "Clean",
		Character,
		"Nominal",
		Formula( Regex( :Original, "sgl", "Sgl", IGNORECASE, GLOBALREPLACE ) ),
		Set Selected,
		Set Display Width( 72 )
	)
)