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
jasongao
Level II

How to extract part of char string in a column by using formula

For example, I have a char column

 

Column 1

Cordinate1:N23; Cordinate2:S45

Cordinate1:S78; Cordinate2:N85

Cordinate1:N67; Cordinate2:N90

Cordinate1:S33; Cordinate2:S74

 

Now I am only interested in the infonation for Cordinate2 North, so I would like to create a new column by using some formula to extract the Cordinate2 N data. The results should look like this:

 

Column 1                                             Column2

Cordinate1:N23; Cordinate2:S45        Do not care

Cordinate1:S78; Cordinate2:N85        N85

Cordinate1:N67; Cordinate2:N90        N90

Cordinate1:S33; Cordinate2:S74        Do not care

 

Thanks a lot!

2 ACCEPTED SOLUTIONS

Accepted Solutions

Re: How to extract part of char string in a column by using formula

Use this expression in a column formula:

 

Regex( text, "Cordinate2:(N\d+)", "\1" );

View solution in original post

vince_faller
Super User (Alumni)

Re: How to extract part of char string in a column by using formula

Here's a column formula that will give you only North Coordinates.  It makes a few assumptions like they'll always be in this format.  

 

txt = Word( 2, Substitute( :Column 1, "Cordinate1", "", "Cordinate2", "" ), ":; " );
If( Left( txt, 1 ) == "N",
	txt,
	""
);
Vince Faller - Predictum

View solution in original post

5 REPLIES 5

Re: How to extract part of char string in a column by using formula

Use this expression in a column formula:

 

Regex( text, "Cordinate2:(N\d+)", "\1" );
jasongao
Level II

Re: How to extract part of char string in a column by using formula

Thanks Mark. Could you please explain the formula a little bit? so I can adapt it to my real data table. For example, sometimes I need use Cordinate1 North as serach pattern and exact Cordinate 2 info, like the results below. How can I adapt the formula? Thank you!

 

Column 1                                             Column2

Cordinate1:N23; Cordinate2:S45        S45

Cordinate1:S78; Cordinate2:N85        Do not care

Cordinate1:N67; Cordinate2:N90        N90

Cordinate1:S33; Cordinate2:S74        Do not care

Re: How to extract part of char string in a column by using formula

I will assume that you know nothing about regular expressions. The character function Regex( "text", "regular expression", "result format" ) matches patterns defined by the regular expression in the text and returns the result as defined by the format argument. You can refer to this topic in Help > Books > Scripting Guide for more details. An alternative approach could be built around JMP patterns and their related functions. The Scripting Guide also covers that topic.

 

How does the need for this adaptability arise? Does it depend in the data problem (i.e., a new problem and new data set) or does it have to handle more than one condition in the same result data column?

 

The regular expression are generally quite specific, so the adaptation would require specifying all of the conditions to be found.

vince_faller
Super User (Alumni)

Re: How to extract part of char string in a column by using formula

Here's a column formula that will give you only North Coordinates.  It makes a few assumptions like they'll always be in this format.  

 

txt = Word( 2, Substitute( :Column 1, "Cordinate1", "", "Cordinate2", "" ), ":; " );
If( Left( txt, 1 ) == "N",
	txt,
	""
);
Vince Faller - Predictum
jasongao
Level II

Re: How to extract part of char string in a column by using formula

Thanks!