topic Cols utilities: how to let <text to columns> take care of empty data? in Discussions

Cols utilities: how to let take care of empty data?

steven_gong — Thu, 26 Mar 2020 05:29:21 GMT

I got a problem when using Cols->utilities->text to columns.

For example I have data 1, ,2,3, after doing text to columns, I want to get {1},{ },{2},{3} these four columns, while JMP gives me {1},{2},{3} as below screen shot.

Excel can do this with <Text to Columns>

Is there an solution out there in JMP?

The result I got from JMP:

what I expected:

Re: Cols utilities: how to let take care of empty data?

txnelson — Thu, 26 Mar 2020 06:35:38 GMT

I am not aware of doing what you want with the Text to Columns() function, but here is a simple function that will do what you want

Names Default To Here( 1 );
dt = New Table( "Example",
	Add Rows( 3 ),
	New Column( "string",
		Character,
		"Nominal",
		Set Values( {"1,2,3", "1,2,3,4", "1,,3,4"} ),
		Set Display Width( 44 )
	)
);

newTexttoColumns = Function({dt,x},{defaultlocal},
start = N Cols( dt );
For( theRow = 1, theRow <= N Rows( dt ), theRow++,
	theWord = "";
	wordCnt = 1;
	For( i = 1, i <= Length( Column( x )[theRow] ), i++,
	
		If( Substr( Column( x )[theRow], i, 1 ) == "," | i == Length( Column( x )[theRow] ),
			If(i == Length( Column( x )[theRow]),theWord = theWord || Substr( Column( x )[theRow], i, 1 ));
			If( N Col( dt ) < start + wordCnt,
				dt << New Column( "", character )
			);
			Column( start + wordCnt )[theRow] = theWord;
			wordCnt++;
			theWord = "";
		,
			theWord = theWord || Substr( Column( x )[theRow], i, 1 )
		)
	
	);
);
0;
);

return = newTexttoColumns(dt,"string");

Re: Cols utilities: how to let take care of empty data?

steven_gong — Sat, 28 Mar 2020 02:41:57 GMT

Hi Jim,

What a speed to deliver such a cool script!

Thanks a lot.

BTW, I think JMP may need to consider to enhance the function of <text to columns>.

Re: Cols utilities: how to let take care of empty data?

txnelson — Sat, 28 Mar 2020 02:43:35 GMT

If you think JMP should have the enhancement, please add it to the JMP Wish List

Re: Cols utilities: how to let take care of empty data?

hogi — Tue, 27 Jun 2023 20:47:20 GMT

I recently had the same issue and wondered if a checkbox can be added to the tool:
Then the user can decide on his own if he wants to enable/disable the feature to treat multiple separator character as a single one: