topic Re: Using Word() or Words() function to parse string in a column in Discussions

Using Word() or Words() function to parse string in a column

JMPnoob — Sun, 11 Jun 2023 00:00:43 GMT

Hello all,

I have a table where one of the columns had data that is in the form of an alphanumeric string (e.g. 1bc5ef-gh7) and I would like to parse the string using the hyphen as the delimiter and they return the first part of the string (1bc5ef) as data in a new column.

So far, I have the code below. If I use the Words() function, it only returns the second part of the of the string (e.g. gh7). However, if I use the Word() function, the RES list comes up empty.

I would like to get the first part of the string and put it into a new column in the same table.

// there is a data table that already has a column called new_batch

	Vals = :new_batch << Get Values;  // Get Values out 
	Res = List(); // Define empty list 

For(i = 1 , i <= N Items(Vals), i++,
	 Test = Words(Vals[i],"-"); // Words is the function that will split your string by delimiter 
	  Insert Into(Res,Test[N Items(Test)]);
   );
   dt << New Column("new_batch_2", RES);

Any help is appreciated. Thanks,

Re: Using Word() or Words() function to parse string in a column

jthi — Thu, 20 Apr 2023 17:37:08 GMT

Try using 1 for the index instead of N Items() if you want the first index (with Words()) and with Word() the syntax should be something like Word(1, Vals[i], "-")

Names Default To Here(1);

str = "1bc5ef-gh7";

w1 = Words(str, "-");
show(w1);
show(w1[1]);

w2 = Word(1, str, "-");
show(w2);

Re: Using Word() or Words() function to parse string in a column

vince_faller — Thu, 20 Apr 2023 19:53:31 GMT

JMP has a builtin text to columns() function.

Names default to here(1);
dt = New Table("split", 
	<< New Column("new_batch", character, <<Set Values({"1bc5ef-gh7"}))
);

dt << Text To Columns(
	delimiter( "-" ),
	columns( :new_batch )
);

Re: Using Word() or Words() function to parse string in a column

JMPnoob — Thu, 20 Apr 2023 21:19:49 GMT

Thanks for the help. I ended up using the code below to split the data into a new column separated by the delimiter.

 dt3 << Text To Columns( delimiter( "-" ), columns( :new_batch )); // strips out the bottle number from BCB batch numbers. Other chemicals not affected

It gives the following output

many thanks for the help.

Re: Using Word() or Words() function to parse string in a column

JMPnoob — Thu, 20 Apr 2023 21:21:29 GMT

Thanks for the help. The example with word() and words() worked. However, I ended up using the code below because it required only 1 line and was simpler.

 dt3 << Text To Columns( delimiter( "-" ), columns( :new_batch )); // strips out the bottle number from BCB batch numbers. Other chemicals not affected