Subscribe Bookmark RSS Feed

How to extract characters in occurrences of a certain character

jyw

Community Trekker

Joined:

Jul 4, 2016

for instance, I have a string of "JN-36M-GRNNC", I would like to extract "JN", "36M and "GRNNC" by using formula. The length between "-" is not fixed, can vary. I found an example on how to do it in Excel, https://www.ablebits.com/office-addins-blog/2015/10/07/excel-find-search-functions/. I wonder how to do it in JMP.


Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
Solution

hi jyw​,

there are many ways of doing this pending on your further use. if there is always the same delimiter (such as "-") this method should do the trick.

otherwise it is not robust.

best,

ron

New Table( "Untitled",

       Add Rows( 1 ),

       New Column( "Column 1", Character, Nominal, Set Values( {"JN-36M-GRNNC"} ) ),

       New Column( "Column 2",

              Character,

              Nominal,

              Formula( Word( 1, :Column 1, "-" ) )

       ),

       New Column( "Column 3",

              Character,

              Nominal,

              Formula( Word( 2, :Column 1, "-" ) )

       ),

       New Column( "Column 4",

              Character,

              Nominal,

              Formula( Word( 3, :Column 1, "-" ) )

       )

);

2 REPLIES
Solution

hi jyw​,

there are many ways of doing this pending on your further use. if there is always the same delimiter (such as "-") this method should do the trick.

otherwise it is not robust.

best,

ron

New Table( "Untitled",

       Add Rows( 1 ),

       New Column( "Column 1", Character, Nominal, Set Values( {"JN-36M-GRNNC"} ) ),

       New Column( "Column 2",

              Character,

              Nominal,

              Formula( Word( 1, :Column 1, "-" ) )

       ),

       New Column( "Column 3",

              Character,

              Nominal,

              Formula( Word( 2, :Column 1, "-" ) )

       ),

       New Column( "Column 4",

              Character,

              Nominal,

              Formula( Word( 3, :Column 1, "-" ) )

       )

);

jyw

Community Trekker

Joined:

Jul 4, 2016

hi Ron,

This is great, I got it working as expected, thank you so much!