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

caspears

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.

 

uday_guntupalli

@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"<BR />        )<BR />