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
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, ":;" ) )
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);
)
);
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, ":;" ) )
Thank you! Both versions did exactly what I was looking for