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
Text to Columns, Version 3
Jeff_Perkinson
Community Manager Community Manager

Text to Cols JMP Add-In

Written by Jeff Perkinson

 

Version 3 now adds a callable JSL function. See Version notes below for details.

 

6566_PagesScreenSnapz001.png

 

Text to Cols creates new columns from a column of delimited text. As shown above, the column “Data” has a comma-delimited string. Text to Cols creates the six new columns to the right.

6567_JMPScreenSnapz001.png

 

After installing the Text to Cols add-in, you’ll find a new menu at the bottom of the Cols menu.

 

When you select this menu item, you’ll be prompted to choose a column of text and specify the delimiter.

 

6568_TextToCols2.png

Select your column and click OK.

 

New columns are added to the data table to contain the values from your column. Each new column has a formula using the Word() function to derive its value.

 

 

 

Version 2 updates

 

This version is built with the Application Builder. It uses a faster algorithm making it more efficient when dealing with lots of rows. It will also work on multiple columns at once.

 

Version 3 updates

 

Support for calling Text To Cols from JSL by adding a com.jmp.jperk namespace with a function.

 

com.jmp.jperk:texttocolumns( column, delimiters, <data table=current data table()>)

 

Description
   Creates new columns in data table from a column of text using delimiters to define each column

 

 

Returns
   An associative array
{{”columns”, list of columns created}, {”name”, text string with name of column group containing columns}}

 

Arguments

column a JSL column reference of a text column to be split into multiple columns

delimiters a string of characters to be used as delimiters

data table optional reference to a data data table that column is in and to which should the new columns will be added. Current Data Table() will be used if no data table is specified.

 

Example:

 

bc_party = Open( "$SAMPLE_DATA\Consumer Preferences.jmp" );
com.jmp.jperk:texttocolumns( Column( bc_party , "floss delimited"), ",", bc_party );

 

Support

 

For help, comments, suggestions, questions, complaints or compliments, please email Jeff.Perkinson@jmp.com.

Comments
mjoner

Hi Jeff, I think it would be easy to add a modification to this to allow multiple columns to be selected to be run through the Text to Columns algorithm. Is this something you're interested in doing?

Good suggestion, mjoner.

I've updated the add-in to do support multiple columns.

-Jeff

m100psi

Installed very easily, but nothing happens on my system when I click on the Text to Columns menu item.

Matt

I'm sorry for the difficulty. Rather than trying to debug it here in public I've sent you a direct message with some questions.

We can post back here once we figure out what's going wrong.

-Jeff

How can I get this add-in?

At the top there are two files attached. The second one *.jmpaddin needs to be saved somewhere. Open JMP and pick the file and drop it on a JMP window. JMP will ask you to install the add-in. Just follow the process and you shouldn't fail. If you want to read more about add-ins refer to the documentation within JMP under

Books -> Using JMP.pdf, search for JMP add-in or simply add-in.

cheers,

Martin

Thank you very much.

Francisco

Hi Jeff,

Thanks! The add-in works fantastically! Is there a function for this add-in that can be used in a script?

 

Francisco

@Francisco, thanks for the comment. I've updated the add-in to Version 3 which includes a JSL function that you can call from your own script. Let me know how it works for you.

Vball247

Just wanted to note that in JMP12 and beyond, looks like a "text to column" option was added. After selecting one or more columns, can be found under Cols > Utilities > Text to Columns. One of our JMP users wanted to add this as an add-in to our most popular add-in menu, but we found the menu item at same time. Still comparing the two. One difference is that the add-in defaults to the comma as delimiter, while the built in menu item defaults to nothing, so you have to type in the comma. I am wondering if this can be set in Preferences but don't see it, so may request for JMP15EA. Thanks for this topic because it often comes up for new users learning JMP compared to what they were used to in Excel, where you often had to use the "text to columns" function.

Thanks for your comment. I'm glad that you've found this useful.

 

Indeed, since JMP 12, Text to Columns is now built-in. Feel free to add your suggestion for a default delimiter to the JMP Wish List.

abmayfield

I have used this no less than 2,000 times (since I am dealing with large strings of text associated with sequencing data), but I do see that the recode platform of JMP 15 and 16 (beta) is becoming more sophisticated, so I'm guessing most of the text delimiting needs will eventually be met by the recode platform.