BookmarkSubscribeRSS Feed
osbornsarah9

Community Trekker

Joined:

Feb 9, 2012

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

Joined:

Jun 23, 2011

Solution

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

3 REPLIES
pmroz

Super User

Joined:

Jun 23, 2011

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

Joined:

Jun 23, 2011

Solution

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

Community Trekker

Joined:

Feb 9, 2012

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