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

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

osbornsarah9 — Tue, 21 May 2013 19:30:21 GMT

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

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

pmroz — Tue, 21 May 2013 19:59:47 GMT

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

)

);

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

ms — Tue, 21 May 2013 20:10:12 GMT

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

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

osbornsarah9 — Tue, 28 May 2013 20:31:09 GMT

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