49068_en-US

liliconcom

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.

 

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

It worked thanks a lot!

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

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

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:

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 )