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]

Look into the "Text to Columns" function and the Word() function to accomplish what you are asking about

[#3]

Hi, I am running JMP 13 and have the Text to Columns add-in installed and can use it from the menus.

Now, I have a data table character column which represent spectrum data from an spectrophotometer. There are in total 138 substrings separated with commas. I want to code in JSL, using the add-in, the generation of 138 numerical columns each with one value in it. (Since the data is from a diode array, the wavelengfh separation is constant,so I do not have it in the data, and therefore do not need to convert that..)

I've read some of the examples on how to do this, but I fail to get the right syntax.

A little push of help would be appreciated: What is the syntax for generating 138 columns from a specific column using the add-in?

Thanks in advance

Poul R S

[#4]

Here is the example from the Addin's documentation.  Make sure you have installed version 3 of the Addin, and then run this example.

bc_party = Open( "$SAMPLE_DATA\Consumer Preferences.jmp" );

com.jmp.jperk:texttocolumns( Column( bc_party , "floss delimited"), ",", bc_party );

If it works, then using this code is how you incorporate it into your JSL

[#5]

Hi again, thanks a lot. It works and my data gets split up. There are now 138 columns with names SPDATA 1 - SPDATA 138.

I know it is a little bit outside the thread theme and perhaps something I should be able to, but:

How can I in a compact JSL statement set all column properties to numeric and continous?

And secondly, how can I create a transformed data table, where the spectrumdata is ONE column with multilpes of 138 rows according to some categorial data also available (datetime values that have the same value for all 138 datapoints).

Sincerely Poul R S

[#6]

Your compact code for setting the Data Type and Modeling Type for all columns would be

dt = current data table();
for( i = 1, i <= NCols( dt ), i++,
     column( dt, i ) << data type( "numeric" ) << modeling type( "continuous" );
);

Concerning your transpose, you should be able to get what you want using the Stack Platform

     Tables==>Stack

[#7]

Jim answered your question, but you don't need a script.

1. Select all the columns to be changed.
2. Select Cols > Standardize Attributes.
3. Click Attributes and select Data Type.
4. Click Attributes and select Modeling Type.
5. Change both of these attributes the way you want them.
6. Click OK.

Scripting is great but JMP is optimized for interaction.

[#8]

Hi Jeff,

This "split" and "stack" function in JMP is really nice. I don't suppose you know the SQL equivalent or a way to do that in SQL? If that's not advice you give that's fine. Just save me looking at SQL boards.

[#9]

Can you supply some example data with before and after looks for splitting and stacking?