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

[#1]

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

What I want to have:

Image 2

[#2 · Accepted solution]

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

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.

[#3]

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

[#4]

How can this be written in code/JSL?

[#5]

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],".");
);

[#6]

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

[#7]

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?

[#8]

In my build of 13.1, I see:

[#9]

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

[#10]

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