Choose Language Hide Translation Bar
Highlighted
Level II

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

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
Highlighted
Level VIII

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

@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
5 REPLIES 5
Highlighted
Level VIII

## 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
Highlighted
Level II

## 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:

Highlighted
Level VIII

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

@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
Highlighted
Super User

## 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
Highlighted
Level II

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

It worked thanks a lot!

Article Labels

There are no labels assigned to this post.