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
- :
- How do I write a formula to population Column B based on a partial string in Col...

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

Highlighted

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

Dec 27, 2017 11:01 AM
(4690 views)

For example:

Column A has

University of Hawaii

Hawaii Community College

Column B is currently blank, it is new. I want to populate it based on Column A's data.

For example...

If Row A has a string with "University" in it then I want Row B to say "University"

If Row A has a string with "Community" in it, then I want Row B to print "Community"

and I want this to happen for every instance of University or Community.

1 ACCEPTED SOLUTION

Accepted Solutions

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

@caspears ,

The script I provided had multiple portions .

Portion 1 :

Create a new table with sample data to demonstrate

Portion 2 :

Actual formula that you seek

In your case , you essentially only need the part in the Formula

```
dt = New Table(); // Create a new table - you dont need this
dt << New Column("Col1",Character,Continuous,<< Set Values({"University of Toronoto","Community College of Tempe","Temple University","Arizona University","Community College of Wisconsin"})); // Create a column with sample data for demo - you dont need this
DesList = {"University","university","Community","Community"}; // you actually dont need this
dt << New Column("Check",Character,Continuous,Formula(If(!IsMissing(Regex(Char(:Col1),".*[uU]niversity*")),
"University";
,
If(!IsMissing(Regex(Char(:Col1),".*[cC]ommunity*")),
"Community";
)
)
)
); // In this part of the script all you need is the part under "Formula"
```

Here is the part you need. You can't use :Col1 in this. It needs to be replaced by the Column you are performing the search on

`If(!IsMissing(Regex(Char(:Col1),".*[uU]niversity*")), "University"; , If(!IsMissing(Regex(Char(:Col1),".*[cC]ommunity*")), "Community"`

)`)`

Best

Uday

Uday

5 REPLIES 5

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

Re: How do I write a formula to population Column B based on a partial string in Column A?

@caspears,

You can use Contains() or Regex() to do this . I will provide an example with Regex()

```
dt = New Table();
dt << New Column("Col1",Character,Continuous,<< Set Values({"University of Toronoto","Community College of Tempe","Temple University","Arizona University","Community College of Wisconsin"}));
DesList = {"University","university","Community","Community"};
dt << New Column("Check",Character,Continuous,Formula(If(!IsMissing(Regex(Char(:Col1),".*[uU]niversity*")),
"University";
,
If(!IsMissing(Regex(Char(:Col1),".*[cC]ommunity*")),
"Community";
)
)
)
);
```

For a little more explanation on "Regular Expressions" - look here - https://community.jmp.com/t5/Discussions/Regex-help/m-p/48759#M27716

or follow Craige Hales blog here (https://community.jmp.com/t5/Uncharted/Regex/ba-p/21008/jump-to/first-unread-message )

Best

Uday

Uday

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

Re: How do I write a formula to population Column B based on a partial string in Column A?

Hi:

It's not doing anything:

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

@caspears ,

The script I provided had multiple portions .

Portion 1 :

Create a new table with sample data to demonstrate

Portion 2 :

Actual formula that you seek

In your case , you essentially only need the part in the Formula

```
dt = New Table(); // Create a new table - you dont need this
dt << New Column("Col1",Character,Continuous,<< Set Values({"University of Toronoto","Community College of Tempe","Temple University","Arizona University","Community College of Wisconsin"})); // Create a column with sample data for demo - you dont need this
DesList = {"University","university","Community","Community"}; // you actually dont need this
dt << New Column("Check",Character,Continuous,Formula(If(!IsMissing(Regex(Char(:Col1),".*[uU]niversity*")),
"University";
,
If(!IsMissing(Regex(Char(:Col1),".*[cC]ommunity*")),
"Community";
)
)
)
); // In this part of the script all you need is the part under "Formula"
```

Here is the part you need. You can't use :Col1 in this. It needs to be replaced by the Column you are performing the search on

`If(!IsMissing(Regex(Char(:Col1),".*[uU]niversity*")), "University"; , If(!IsMissing(Regex(Char(:Col1),".*[cC]ommunity*")), "Community"`

)`)`

Best

Uday

Uday

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

Re: How do I write a formula to population Column B based on a partial string in Column A?

I believe this formula is a simpler version of what @uday_guntupalli was suggesting

```
If(Contains( UpperCase( :Column A, "UNIVERSITY" ), "University",
Contains( UpperCase( :Column A, "COMMUNITY" ), "Community"
);
```

Jim

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

Re: How do I write a formula to population Column B based on a partial string in Column A?

It worked thanks a lot!