Subscribe Bookmark RSS Feed

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
laura_archer

Staff

Joined:

Aug 7, 2015

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, as described by thechadd

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

 

 

12570_JMPScreenSnapz035.png

10 REPLIES
thechadd

Community Trekker

Joined:

Jun 23, 2011

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 :)
thechadd

Community Trekker

Joined:

Jun 23, 2011

Since I'm bored:

First Name: Munger( :Name, Munger( :Name, 1, ", " ) + 2, -1 )
Last Name: Munger( :Name, 0, Munger( :Name, 1, ", " ) - 1 )
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
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

Joined:

Jun 23, 2011

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

Joined:

Jun 23, 2011

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

Message was edited by: PMroz

Message was edited by: PMroz

Message was edited by: PMroz

For some reason not all of the code is being displayed. Working with tech support!


Message was edited by: PMroz
pmroz

Super User

Joined:

Jun 23, 2011

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

Super User

Joined:

Jun 23, 2011

OK I figured out how to post - had to substitute HTML equivalents for double quotes and the Less-Than character. 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);

Message was edited by: PMroz
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.