BookmarkSubscribe
Choose Language Hide Translation Bar
Highlighted
osbornsarah9
Community Trekker

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

0 Kudos
1 ACCEPTED SOLUTION

Accepted Solutions
ms
Super User ms
Super User

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

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

                    )

                );

0 Kudos
ms
Super User ms
Super User

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

0 Kudos
osbornsarah9
Community Trekker

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

0 Kudos