Extract numbers of varying lengths from a string into different columns

[#1]

I have  a column named Duration with values which represent number of days,hours, min and sec. The last m denotes seconds.

So the typical format would be : (0-365)d(0-24)h(0-60)m(0-60)m

Here are some examples.:

0d 15h 47m 43m

10d 10h 21m 54m

0d 04h 07m 32m

268d 06h 02m 33m

I need to extract the number of days, Hours,minutes, and sec in different columns so that I can perform calculations on it.

I tried the formula for extracting the number of days,

 Words( :Duration, "d" )[1] 

For extracting the the second number - I used the formula :

Substr( Item( 2, :Duration ), 1, 2 );

In the same way extracted the third and fourth number using 3 and 4 in the Item formula.

To convert these strings to numeric, I created 4 more columns;copied and pasted and changed the data type to numeric.

I understand this is a round about way to get to the result.

I was wondering if anyone could help me with a more efficient way. 

[#2 · Accepted solution]

How about using Regex:

For the Day:

Num( Regex( :Duration, "(\d+)d\s(\d+)h\s(\d+)m\s(\d+)m", "\1" ) )

For the Hour:

Num( Regex( :Duration, "(\d+)d\s(\d+)h\s(\d+)m\s(\d+)m", "\2" ) )

For the Minute:

Num( Regex( :Duration, "(\d+)d\s(\d+)h\s(\d+)m\s(\d+)m", "\3" ) )

For the Second:

Num( Regex( :Duration, "(\d+)d\s(\d+)h\s(\d+)m\s(\d+)m", "\4" ) )

Each number is in a capturing group although you really don't need to capture the others. You could simplify it with these:

Num( Regex( :Duration, "(\d+)d\s\d+h\s\d+m\s\d+m", "\1" ) )
Num( Regex( :Duration, "\d+d\s(\d+)h\s\d+m\s\d+m", "\1" ) )
Num( Regex( :Duration, "\d+d\s\d+h\s(\d+)m\s\d+m", "\1" ) )
Num( Regex( :Duration, "\d+d\s\d+h\s\d+m\s(\d+)m", "\1" ) )

[#3]

Attached sample table.

[#4]

Paul- I learnt a new function. Thanks a lot for your detailed and simplified version of the solution.!

[#5 · Accepted solution]

This example illustrates how to use regular expressions.

Names Default To Here( 1 );

cell = {"0d 15h 47m 43m", "10d 10h 21m 54m", "0d 04h 07m 32m", "268d 06h 02m 33m"};

For( i = 1, i <= N Items( cell ), i++,
	day = Num( Regex( cell[i], "(\d+)d (\d+)h (\d+)m (\d+)m", "\1" ) );
	Show( day );
);

[#6]

Mark,

  I tried using your script in many different ways 

a) by using the script in the script editor and running it,

b) creating a column with name "cell" and entering the list of items in it + creating another column "day" and using the "for loop" as a formula in that column,

and other ways.

I get an error.

I tried looking around in the scripting index for help too.

 But I cannot get the script to work.  I am new to scripting. Can you please help.

[#7]

@Mark_Bailey script is a standalone JSL.  You run it by putting it into a script window and running the script.  I did that and it worked great, giving the following results in the JMP log

day = 0;
day = 10;
day = 0;
day = 268;

[#8]

Thanks a lot for pointing me to the log. I saw the result there.Previously I was thinking that the result will show up in a separate window.

[#9 · Accepted solution]

@txnelson is correct. He showed you how to use it as a separate script. This expression is all that you need for a column formula:

Num( Regex( cell[i], "(\d+)d (\d+)h (\d+)m (\d+)m", "\1" ) )

[#10]

Mark

     I was under the impression that the result will show up in a separate window. Now, I looked into the log- and I saw the result there,- as many times I had run the script.