turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Discussions
- :
- Re: How to extract part of char string in a column by using formula

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jan 8, 2019 9:40 AM
(169 views)

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

Highlighted
Solution

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Use this expression in a column formula:

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

Learn it once, use it forever!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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,
""
);
```

5 REPLIES

Highlighted
Solution

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Use this expression in a column formula:

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

Learn it once, use it forever!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.

Learn it once, use it forever!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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,
""
);
```

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Thanks!