BookmarkSubscribe
Choose Language Hide Translation Bar
Highlighted
Thomas1
Community Trekker

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.

0 Kudos
2 ACCEPTED SOLUTIONS

Accepted Solutions
gzmorgan0
Super User

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

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?

0 Kudos
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
0 Kudos
Thomas1
Community Trekker

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.

0 Kudos
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

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

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.


 

0 Kudos
Thomas1
Community Trekker

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?

0 Kudos