BookmarkSubscribe
Choose Language Hide Translation Bar
Highlighted
caspears
Community Trekker

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.

 

0 Kudos
1 ACCEPTED SOLUTION

Accepted Solutions
uday_guntupalli
Community Trekker

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
0 Kudos
5 REPLIES 5
uday_guntupalli
Community Trekker

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
caspears
Community Trekker

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:

JMPHelp.jpg

0 Kudos
uday_guntupalli
Community Trekker

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

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

It worked thanks a lot!

0 Kudos