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

How to split a column cells into different columns

Not sure how I can split a column of cells into different columns. For example I have this ONE column with both First and Last names as one cell

Name
1 Lee, Bob
2 Miller, Mike
3 Smith, Jack

Not split this one column into Two columns, one of First, and second column of Last

Last First
1 Lee Bob
2 Miller Mike
3 Smith Jack
1 ACCEPTED SOLUTION

Accepted Solutions

Re: How to split a column cells into different 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, as described by thechadd

Last Name: Word( 1, :Name, ", " )
First Name: Word( 2, :Name, ", " )

 

 

12570_JMPScreenSnapz035.png

View solution in original post

12 REPLIES 12

Re: How to split a column cells into different columns

There are a couple ways to tackle this. The easiest way is to use the "Word" function.
Assuming your full names are in Column "Name":

Last Name: Word( 1, :Name, ", " )
First Name: Word( 2, :Name, ", " )

The harder way is to do it with the "munger" function :)

Re: How to split a column cells into different columns

Since I'm bored:

First Name: Munger( :Name, Munger( :Name, 1, ", " ) + 2, -1 )
Last Name: Munger( :Name, 0, Munger( :Name, 1, ", " ) - 1 )

Re: How to split a column cells into different columns

Thanks. I am a newbie to JMP. Actually my situation is a little more complicated. Each of my row has a column of data that contains varying # of data which I want to split out. Pls see below

Name Classes
1 Jack a, b, f, h
2 Bill b
3 Steve e, r, t
4 Mary a, b, c, d, e

Since the # of classes each person has taken are varying, what is the correct way to break the classes column out into something like below


Name Classes
1 Jack

Re: How to split a column cells into different columns

Thanks. I am a newbie to JMP. Actually my situation is a little more complicated. Each of my row has a column of data that contains varying # of data which I want to split out. Pls see below. The Classes column is one column with "a,b,f" being one character string

Name Classes
1 Jack a, b, f,
2 Bill b
3 Steve e, r, t
4 Mary a, b, c, d

Since the # of classes each person has taken are varying, what is the correct way to break the classes column out into something like below


Name Classes
1 Jack a
2 Jack b
3 Jack f
4 Bill b
5 Steve e
6 (and so on).

Appreciate any insight. Thanks
ms
Super User (Alumni) ms
Super User (Alumni)

Re: How to split a column cells into different columns

I suggest you export your file as tab-delimited text and then reimport (select "Open" from File menu and "Open as: Data (using preview)") using comma and spaces as field delimiters.
Then you use "Stack" from the tables menu and choose to stack all columns in your imported data table except the name column (make sure "drop non-stacked columns" is un-checked).

This would result in the expanded table you wanted, except for a number of empty rows. Those can easily be deleted after sorting.
pmroz
Super User

Re: How to split a column cells into different columns

I'm doing something very similar, where several columns have lists of varying lengths. Here's a solution adapted from my code:

current_dataset = current data table();
dataset_name = current_dataset << Get Name;

name_list = {};
class_list = {};

nR = NRow(current_dataset);

For (i = 1, i <= nR, i++,

split_classes = Words(Column("Classes"), ",");
nclasses = NItems(split_classes);

/* Add the parsed classes to the longer class list */
insertinto(class_list, split_classes);

/ Add Name to its list multiple times (same # as # of classes) */
For (k = 1, k <= nclasses, k++,
insertinto(name_list, (current_dataset:Name));
)
);

/* OK now we've created lists in memory containing Name and class data. Now
convert these to a dataset */
class_dataset = New Table(dataset_name || " (Classes)");
class_dataset << Minimize Window;

class_dataset << New Column("Name", Character);
class_dataset << New Column("Class", Character);

/* Add lists to their respective columns */
class_dataset:Name << values(name_list);
class_dataset:Class << values(class_list);
pmroz
Super User

Re: How to split a column cells into different columns

My posting got chopped off for some reason. I'm working with tech support to resolve the issue.
pmroz
Super User

Re: How to split a column cells into different columns

OK I figured out how to post. Here's the code:

current_dataset = current data table();
dataset_name = current_dataset << Get Name;

name_list = {};
class_list = {};

nR = NRow(current_dataset);

For (i = 1, i <= nR, i++,

// Parse the list of Classes using , as the delimiter. Take out extra spaces
split_classes = Words(substitute(Column("Classes")[i], ", ", ","), ",");
nclasses = NItems(split_classes);

// Add the parsed classes to the longer class list
insertinto(class_list, split_classes);

// Add Name to its list multiple times (same # as # of classes)
For (k = 1, k <= nclasses, k++,
insertinto(name_list, (current_dataset:Name)[i]);
)
);

// OK now we've created lists in memory containing Name and class data. Now
// convert these to a dataset
class_dataset = New Table(dataset_name || " (Classes)");
//class_dataset << Minimize Window;

class_dataset << New Column("Name", Character);
class_dataset << New Column("Class", Character);

// Add lists to their respective columns
class_dataset:Name << values(name_list);
class_dataset:Class << values(class_list);

\

Re: How to split a column cells into different columns

Apology for being late in thanking everyone. I ended up doing something similar to the first suggestion of using excel to break up the contents on one column and then import everything into JMP and then used stack function. I am not good at writing formulas or scripts, so good thing to learn from the more knowledgeable. Thanks again.