Subscribe Bookmark RSS Feed

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

osbornsarah9

Community Trekker

Joined:

Feb 9, 2012

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
Solution

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

3 REPLIES
pmroz

Super User

Joined:

Jun 23, 2011

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

                    )

                );

Solution

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

Community Trekker

Joined:

Feb 9, 2012

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