cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
osbornsarah9
Level I

How to extract column text based on character values instead of numerical values?

Hi JMP Community,

I have text in a column titled Description which looks like the following:

                                   Description Column

Row 1:     Software Issue: System Software; No Response

Row 2:     Software Issue: System Software; No Response

Row 3:     Electronics Issue: Power Supply; Response 1

Row 4:     Computer and User Interface Issue: System Software; Response 2

Okay, so I want to take the text in this column and split based on the punctuation.  So in a new column I want the text shown before the colon ":", and in another column, I want the text between the semi colon and colon ":" and ";":

So I should get:

                                             Description Column                              Column B                                  Column C

Row 1:     Software Issue: System Software; No Response          Software Issue                      System Software

I've created these new columns, and applied a formula where I tried using the character functions Left/Right (I refer to the Description Column as the text field).  However, all the character functions base length on a numerical value.  How can I use a Left/Right Character function to say

"Right(Description Column, ":")" so that I get all text before the ":"??

Thanks for your help,

-Sarah

1 ACCEPTED SOLUTION

Accepted Solutions
ms
Super User (Alumni) ms
Super User (Alumni)

Re: How to extract column text based on character values instead of numerical values?

An alternative approach here is to use the Word() function.

Formula for Column B:

Word( 1, :Description Column, ":;" )

Formula for Column C:

Trim( Word( 2, :Description Column, ":;" ) )

View solution in original post

3 REPLIES 3
pmroz
Super User

Re: How to extract column text based on character values instead of numerical values?

This will do what you need.  The trick is to use contains and substr to get the exact substrings that you want.

dt = New Table( "Issues",

    Add Rows( 4 ),

    New Column( "Description",

        Character,

        Nominal,

        Set Values(

            {"Software Issue: System Software; No Response",

            "Software Issue: System Software; No Response",

            "Electronics Issue: Power Supply; Response 1",

            "Computer and User Interface Issue: System Software; Response 2"}

        )

    )

);

dt << new column("Column B", Character, Nominal,

                    formula(

                        colon_pos = contains(:Description, ":");

                        substr(:Description, 1, colon_pos - 1);

                    )

                );

dt << new column("Column C", Character, Nominal,

                    formula(

                        colon_pos = contains(:Description, ": ");

                        semi_pos  = contains(:Description, "; ");

                        substr(:Description, colon_pos + 2, semi_pos - colon_pos - 2);

                    )

                );

ms
Super User (Alumni) ms
Super User (Alumni)

Re: How to extract column text based on character values instead of numerical values?

An alternative approach here is to use the Word() function.

Formula for Column B:

Word( 1, :Description Column, ":;" )

Formula for Column C:

Trim( Word( 2, :Description Column, ":;" ) )

osbornsarah9
Level I

Re: How to extract column text based on character values instead of numerical values?

Thank you! Both versions did exactly what I was looking for