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
Thomas1
Level V

How to generate new columns containing parts of a string

What is the best way to create new columns, containing substrings from an existing column? In case the order of the sub strings is always the same, the Word function would be the way to go.

What would be the solution in case the sub strings sequence is not always the same? The attached file shows examples.

2 ACCEPTED SOLUTIONS

Accepted Solutions
gzmorgan0
Super User (Alumni)

Re: How to generate new columns containing parts of a string

Thsi is just an add on to Jeff's response.

 

You can also do this with a script. Note the column names are the target column name, with a counter and appear immediately after the target column. The attached script shows a few JSL statements (there are other methods as well) to get a handle to how many new columns, etc.

 

 

dt << Text to Columns( delimiter( "," ), columns( :ID ) );

 

However, if you are looking for specific terms like POR and 10LPM, you might have to use other methods like regular expressions or functions like If(Contains(:ID, "POR"), "POR", etc...

 

It appears line 4 has a different pattern for what appears to be factors or conditions.

View solution in original post

Thomas1
Level V

Re: How to generate new columns containing parts of a string


@Jeff_Perkinson wrote:

Try Cols->Utilities->Text to Cols. That will take your column and automatically make multiple columns out of it.

 

JMPScreenSnapz257.pngJMPScreenSnapz258.png



Thanks. That's very handy. JMP has so much great build in features. But it can be tricky to discover them.

 

What would a formula or script solution look like from your point of view?

View solution in original post

6 REPLIES 6
txnelson
Super User

Re: How to generate new columns containing parts of a string

What are the rules for the data?  Before one can determine what JMP function(s) to use, one has to know the rules on how to pull the data apart.

 

Are you just trying to find if the "POR" value lexists?  What are you looking for?

Jim
Thomas1
Level V

Re: How to generate new columns containing parts of a string

The goal is to create a column for each substring from the ID column. Within the ID column the substrings are separated by a coma.

Jeff_Perkinson
Community Manager Community Manager

Re: How to generate new columns containing parts of a string

Try Cols->Utilities->Text to Cols. That will take your column and automatically make multiple columns out of it.

 

JMPScreenSnapz257.pngJMPScreenSnapz258.png

-Jeff
gzmorgan0
Super User (Alumni)

Re: How to generate new columns containing parts of a string

Thsi is just an add on to Jeff's response.

 

You can also do this with a script. Note the column names are the target column name, with a counter and appear immediately after the target column. The attached script shows a few JSL statements (there are other methods as well) to get a handle to how many new columns, etc.

 

 

dt << Text to Columns( delimiter( "," ), columns( :ID ) );

 

However, if you are looking for specific terms like POR and 10LPM, you might have to use other methods like regular expressions or functions like If(Contains(:ID, "POR"), "POR", etc...

 

It appears line 4 has a different pattern for what appears to be factors or conditions.

Thomas1
Level V

Re: How to generate new columns containing parts of a string

Thanks. Your script works great too. Your suggestion indicates that the If contain formula is the way to go. Are there any other possibilities?


@gzmorgan0 wrote:

Thsi is just an add on to Jeff's response.

 

You can also do this with a script. Note the column names are the target column name, with a counter and appear immediately after the target column. The attached script shows a few JSL statements (there are other methods as well) to get a handle to how many new columns, etc.

 

dt << Text to Columns( delimiter( "," ), columns( :ID ) );

 

However, if you are looking for specific terms like POR and 10LPM, you might have to use other methods like regular expressions or functions like If(Contains(:ID, "POR"), "POR", etc...

 

It appears line 4 has a different pattern for what appears to be factors or conditions.


 

Thomas1
Level V

Re: How to generate new columns containing parts of a string


@Jeff_Perkinson wrote:

Try Cols->Utilities->Text to Cols. That will take your column and automatically make multiple columns out of it.

 

JMPScreenSnapz257.pngJMPScreenSnapz258.png



Thanks. That's very handy. JMP has so much great build in features. But it can be tricky to discover them.

 

What would a formula or script solution look like from your point of view?