Recode columns containing character value at the end of a column

saneal

Hello,

 

I have a table like

ID	Column2
ABC001235G
ABC001234
ABC001235
ABC001234D
ABC001234F

 

I would like to recode the ID column such that all my values like 'ABC001234D' and 'ABC001234F' become 'ABC001234' - basically I want to drop that last letter in the statement. However, I cannot do this through manual process. There are too many rows to do and I couldn't possibly write it manually for each ID that is possible to be used in the script. Looking for a way to do this through scripting. Thanks!

jthi

For this I would go with regex. I liked this at onepoint to get going with regex at least on some level: regexone also check out JMP Character Functions - Regex and Regex from scripting index.

 

Here is an example with two possible ways to remove end characters using Regex in JMP (they can be easily modified to support non-capital letters also):

 

Names Default To Here(1);

dt = New Table("Untitled 3",
	Add Rows(5),
	Compress File When Saved(1),
	New Column("ID",
		Character,
		"Nominal",
		Set Values(
			{"ABC001235G", "ABC001234", "ABC001235", "ABC001234D", "ABC001234F"}
		)
	)
);


dt << New Column("Regex1", Character, Nominal, Formula(Regex(:ID, "^[A-Z]+(\d+)")));
dt << New Column("Regex2", Character, Nominal, Formula(Regex(:ID, "[A-Z]*$", "", GLOBALREPLACE);));

Depending on the use case choose one (easy to test with the example code how they work with different strings):

First one will match 1 or more capital characters from the start of the string followed by any amount of digits (so it will ignore anything after the digits).

Second one will match zero or more capital characters from the end of string and replace them with empty string.