Subscribe Bookmark RSS Feed

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

tpinck27

Community Trekker

Joined:

Jan 13, 2016

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

Joined:

Jun 23, 2011

Solution

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
8 REPLIES
Jeff_Perkinson

Community Manager

Joined:

Jun 23, 2011

Solution

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

Community Trekker

Joined:

Jan 13, 2016

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

lalvar39

Community Member

Joined:

May 2, 2017

How can this be written in code/JSL?

txnelson

Super User

Joined:

Jun 22, 2012

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

Joined:

Jun 23, 2011

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

-Jeff
pmroz

Super User

Joined:

Jun 23, 2011

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

Joined:

Jun 23, 2011

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

Joined:

Jun 23, 2011

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