Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
caspears
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
uday_guntupalli
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

View solution in original post

5 REPLIES 5
Highlighted
uday_guntupalli
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
caspears
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:

JMPHelp.jpg

Highlighted
uday_guntupalli
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

View solution in original post

Highlighted
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
Highlighted
caspears
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.