Our World Statistics Day conversations have been a great reminder of how much statistics can inform our lives. Do you have an example of how statistics has made a difference in your life? Share your story with the Community!
Choose Language Hide Translation Bar
Highlighted

## 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
Highlighted
Staff

## 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.

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, ", " )

12 REPLIES 12
Highlighted
Level IV

## 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 :)
Highlighted
Level IV

## 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 )
Highlighted

## 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
Highlighted

## 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
Highlighted
Super User

## 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.
Highlighted
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);``````
Highlighted
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.
Highlighted
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);``````

\

Highlighted

## 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.
Article Labels

There are no labels assigned to this post.