BookmarkSubscribeRSS Feed
Choose Language Hide Translation Bar
Thomas1

Community Trekker

Joined:

Feb 16, 2018

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

Joined:

Jul 25, 2016

Solution

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

Community Trekker

Joined:

Feb 16, 2018

Solution

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?

6 REPLIES 6
txnelson

Super User

Joined:

Jun 22, 2012

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

Community Trekker

Joined:

Feb 16, 2018

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

Joined:

Jun 23, 2011

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

Joined:

Jul 25, 2016

Solution

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

Community Trekker

Joined:

Feb 16, 2018

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

Community Trekker

Joined:

Feb 16, 2018

Solution

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?