Choose Language Hide Translation Bar
JulieSAppel
Community Trekker

Converting a text string into duration

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

0 Kudos
1 ACCEPTED SOLUTION

Accepted Solutions

Re: Converting a text string into duration

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:

 

Capture.PNG

 

I attached the modified data table for your review.

 

Learn it once, use it forever!

View solution in original post

4 REPLIES 4

Re: Converting a text string into duration

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:

 

Capture.PNG

 

I attached the modified data table for your review.

 

Learn it once, use it forever!

View solution in original post

Craige_Hales
Staff (Retired)

Re: Converting a text string into duration

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"
			)
		)
	)
)
Craige
JulieSAppel
Community Trekker

Re: Converting a text string into duration

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+"?

0 Kudos
Highlighted
Craige_Hales
Staff (Retired)

Re: Converting a text string into duration

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.

Craige
0 Kudos