Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Aug 9, 2019 7:50 AM
(2173 views)

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

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.

Learn it once, use it forever!

4 REPLIES 4

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.

Learn it once, use it forever!

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Converting a text string into duration

Created:
Aug 12, 2019 12:51 PM
| Last Modified: Aug 12, 2019 12:58 PM
(2088 views)
| Posted in reply to message from JulieSAppel 08-12-2019

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

Article Labels

There are no labels assigned to this post.