Choose Language Hide Translation Bar
Highlighted
steven_gong
Occasional Contributor

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

Hi

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:

steven_gong_1-1585200437035.png

what I expected:

steven_gong_2-1585200470456.png

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

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

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

View solution in original post

3 REPLIES 3
txnelson
Super User

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

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

View solution in original post

Highlighted
steven_gong
Occasional Contributor

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

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>.

0 Kudos
Highlighted
txnelson
Super User

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

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

Jim