nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Converting a text string into duration

JulieSAppel

Hi,

I´m having problems with converting a string into a time period (duration).

One of the problems is the white spaces in the string: There is no white space between "days" and "hours" + number. And there is a white space between minutes + number.

Recording time

9days 21hours 54 minutes

6days 21hours 29 minutes

I´ve tried to use substitute the words to get further ahead but that doesn´t seem to work when there are no white spaces.

I´ve also tried to use "text to columns" but that didn´t work for me either.

Br Julie

Rec_time.jmp

Accepted answers

Mark_Bailey

This case is not too bad. I used the Regex() function. Here is the column formula:

Num( Regex( :Recording time, "(\d+)day", "\1" ) ) * In Days( 1 )
+Num( Regex( :Recording time, "\D+(\d+)hour", "\1" ) ) * In Hours( 1 )
+Num( Regex( :Recording time, "\D+(\d+) minute", "\1" ) ) * In Minutes( 1 )

I applied one of the Duration formats for numerical data to get this result:

I attached the modified data table for your review.

Rec_time.jmp

All comments

Mark_Bailey

This case is not too bad. I used the Regex() function. Here is the column formula:

Num( Regex( :Recording time, "(\d+)day", "\1" ) ) * In Days( 1 )
+Num( Regex( :Recording time, "\D+(\d+)hour", "\1" ) ) * In Hours( 1 )
+Num( Regex( :Recording time, "\D+(\d+) minute", "\1" ) ) * In Minutes( 1 )

I applied one of the Duration formats for numerical data to get this result:

I attached the modified data table for your review.

Rec_time.jmp

Craige_Hales

And a slight variation

New Table( "Rec_time",
	Add Rows( 8 ),
	New Column( "Recording time",
		Character,
		"Nominal",
		Set Values(
			{"9days21hours54minutes", " 6 days 21 hours 29 minutes",
			"2days 21 hours 24 minutes", "13days 20hours04minutes",
			" 9 days 21hours 54 minutes", "9days 21hours 54 minutes",
			"9days 21hours 54 minutes", "6days 20hours 39 minutes"}
		),
		Set Display Width( 240 )
	),
	New Column( "data",
		Numeric,
		"Nominal",
		Format( ":day:hr:m", 14 ),
		Input Format( ":day:hr:m" ),
		Formula(
			Informat(
				Regex(
					:Recording time,
					"(\d+)\s*days\s*(\d+)\s*hours\s*(\d+)\s*minutes",
					":\1:\2:\3"
				),
				":day:hr:m"
			)
		)
	)
)

JulieSAppel

Thanks - that worked. However, just to understand this better: is it even necessary to include the "days, hours, minutes" in the formula?

Also, why do you need "\D+" and not just "\d+"?

Craige_Hales

days, hours, minutes... maybe, maybe not. If you are confident of the data source pattern not changing, you can take shortcuts. If the source pattern of the data might change, perhaps adding white space, \s* will allow optional white space in the future.

Neither of these examples are likely to handle a future source that adds weeks; they will probably silently ignore it. If you forced the pattern to match from beginning to end, using ^ and $, you could probably get a missing value if something unexpected (weeks) shows up in the future.

Mark's approach would be easier to modify to handle weeks. My approach would be easier to add the ^ and $ to test for something unexpected.

I did not run Mark's example, but I think you may be right that the \D isn't needed.

Edit: in Mark's formula there are three independent regex matches, and they depend on the word following the digits to get the right digits. In my formula, something has to skip over the constant text (days,hours,minutes). Using the actual words makes the formula easier to understand.