cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
tpinck27
Level II

How can I split text in a column into multiple columns?

Sometimes I have a data set with information in one column that I would like split across multiple columns. For example in image 1 below I have four identifiers mashed into one column that I would like split into four columns. I want the lot number (11) text identifier (DAT) sample number (001,002 etc) and file type (.tif) all split into individual columns (as shown in image 2). What is the best way to do this in formula editor or using JSL. I've attached a sample data file.

What I have:

Image 1

12568_pastedImage_12.png

What I want to have:

Image 2

12566_pastedImage_10.png

1 ACCEPTED SOLUTION

Accepted Solutions
Jeff_Perkinson
Community Manager Community Manager

Re: How can I split text in a column into multiple columns?

If you're using JMP 12 or later you can use Cols -> Utilities -> Text to Columns.

12569_JMPScreenSnapz034.png

If you're using JMP 11 or earlier you can get the add-in, Text to Columns, Version 2​, which does the same thing as the now, built-in function.

If you want to do this by hand, either with JSL or the Formula Editor, you'll find the Word() function handy. Trust me.

12570_JMPScreenSnapz035.png

-Jeff

View solution in original post

17 REPLIES 17
Jeff_Perkinson
Community Manager Community Manager

Re: How can I split text in a column into multiple columns?

If you're using JMP 12 or later you can use Cols -> Utilities -> Text to Columns.

12569_JMPScreenSnapz034.png

If you're using JMP 11 or earlier you can get the add-in, Text to Columns, Version 2​, which does the same thing as the now, built-in function.

If you want to do this by hand, either with JSL or the Formula Editor, you'll find the Word() function handy. Trust me.

12570_JMPScreenSnapz035.png

-Jeff
tpinck27
Level II

Re: How can I split text in a column into multiple columns?

Perfect, this is exaclty what I was looking for, thanks Jeff.

lalvar39
Level I

Re: How can I split text in a column into multiple columns?

How can this be written in code/JSL?

txnelson
Super User

Re: How can I split text in a column into multiple columns?

Names Default To Here( 1 );
dt = New Table( "sample",
	New Column( "column 0", character, values( {"11 DAT001.tif", "11 DAT002.tif", "11 DAT003.tif", "11 DAT004.tif"} ) )
);
// Pause so you can see it working
Wait( 2 );

// add some as an example of the elements using a formula
dt << New Column( "Column 1", formula( Num( Word( 1, :column 0 ) ) ) );
dt << New Column( "Column 2", character, formula( Substr( Word( 2, :column 0 ), 1, 3 ) ) );
dt << New Column( "Column 3", character, formula( Substr( Word( 2, :column 0 ), 6, 1 ) ) );

// Now do the last one, using JSL code
dt << New Column( "Column 4", character );
For( i = 1, i <= N Rows( dt ), i++,
	:Column 4[i] = Word( -1, :Column 0[i],".");
);
Jim
Jeff_Perkinson
Community Manager Community Manager

Re: How can I split text in a column into multiple columns?

I've recently posted Text to Columns, Version 3 which adds support for calling Text to Columns from JSL.

-Jeff
pmroz
Super User

Re: How can I split text in a column into multiple columns?

The documentation for col << text to columns is en empty stub in the scripting index.  I've tried various combinations but can't get it to work.  Can someone from JMP let us know the proper syntax?

ian_jmp
Staff

Re: How can I split text in a column into multiple columns?

In my build of 13.1, I see:

Screen Shot 2017-05-03 at 15.03.50.pngScreen Shot 2017-05-03 at 15.03.27.png

pmroz
Super User

Re: How can I split text in a column into multiple columns?

Thanks Ian.  I'm using JMP 13.1.0 and that page is blank.

 

So back to the most recent question here's how to call text to columns in JSL:

dt = New Table( "Text2Col Example", Add Rows( 4 ),
	New Column( "Col1", Character, "Nominal", 
		Set Values(
			{"11 DAT 001.tif", "11 DAT 002.tif", "11 DAT 003.tif", "11 DAT 004.tif"}
		)
	),
	New Column( "Col2", Numeric, "Continuous", Format( "Best", 12 ),
		Set Values( [159.4308, 229.5031, 164.3414, 162.0241] )
	)
);

dt << text to columns(delimiter(" "), columns(:Col1));
ENTHU
Level IV

Re: How can I split text in a column into multiple columns?

Hi,

 

Just working on an extension of test to columns feature. I have a string ACE_100_bb_29_icfg2 in a column named item code. Is there a way to separate this text by _ and then only have first 3 characters(ABC) in the column item code. 

The way I am doing it now is - deleting all other columns that is produced by this function that is item code 1 ,2 and so far and renaming the column with ABC as item code. Was wondering if there is an efficient way to do this.

 

Thanks