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.
... View more