Hi team, I have a coloumn with below values , current Coloumn type is V-String PT0H0M17S PT0H0M7S PT00H39M06S These values are showing time. Like 0 hour, 0M, 17 seconds (First value.) My Objectove is to convert this cell value to a new coloumn that shows timestamp. Like PT Duration Timestamp PT0H0M17S00:00:17PT00H39M06S00:39:06PT00H00M20S00:00:20 I was thinking to remove first two ch, split coloumn after H, M to get hours and minutes and seconds. Then remove last CH S using REGEX but I don't know how to use it. Then bring everything together to create time stamp. Can you please assist me, Thanks,

Use REGEX to Split a coloumn with no space

itahir04

Hi team,

I have a coloumn with below values , current Coloumn type is V-String

PT0H0M17S
PT0H0M7S

PT00H39M06S

These values are showing time. Like 0 hour, 0M, 17 seconds (First value.)

My Objectove is to convert this cell value to a new coloumn that shows timestamp.

Like PT

Duration Timestamp

PT0H0M17S	00:00:17
PT00H39M06S	00:39:06
PT00H00M20S	00:00:20

I was thinking to remove first two ch, split coloumn after H, M to get hours and minutes and seconds. Then remove last CH S using REGEX but I don't know how to use it.

Then bring everything together to create time stamp.

Can you please assist me,

Thanks,

Regex

Accepted answers

AniK

You can follow the below steps to achieve the result. I have attached the completed workflow.

1) Use Text to Columns to split the data

2) Use RegEx replace function in formula to strip off text. So only numbers remain

3) Use Date time to get the required output

New Workflow1.yxmd

Loic

Hi @itahir04

In Designer: open the example for the tool RegEX:

Then go there: https://help.alteryx.com/current/boost/syntax_perl.html you will need to understand that type of syntax for this tool.

In your case the pattern of your string is: (PT)(.*)(H)(.*)(M)(.*)(S)

Each group under () is a field .* means any number - it will create 7 fields.

Field 2 contains hour, Field 4 contains Minutes, Field 6 contains seconds

Concatenate using Formula Tool and convert to time stamp using DateTime tool

RegEx.yxmd

ponraj

Sample workflow as below.

Use REGEX to Split a coloumn with no space.yxmd

All comments

AniK

You can follow the below steps to achieve the result. I have attached the completed workflow.

1) Use Text to Columns to split the data

2) Use RegEx replace function in formula to strip off text. So only numbers remain

3) Use Date time to get the required output

New Workflow1.yxmd

Loic

Hi @itahir04

In Designer: open the example for the tool RegEX:

Then go there: https://help.alteryx.com/current/boost/syntax_perl.html you will need to understand that type of syntax for this tool.

In your case the pattern of your string is: (PT)(.*)(H)(.*)(M)(.*)(S)

Each group under () is a field .* means any number - it will create 7 fields.

Field 2 contains hour, Field 4 contains Minutes, Field 6 contains seconds

Concatenate using Formula Tool and convert to time stamp using DateTime tool

RegEx.yxmd

ponraj

Sample workflow as below.

Use REGEX to Split a coloumn with no space.yxmd

itahir04

Thanks Poonraj for providing a very simple solution.

Regards,

Rana

itahir04

Thanks Aloc, great simple logic. THis provides me a perfect solution.

Thanks,

Rana

itahir04

Thanks Anik,

Great way of solving this issue. Great solution.

Thanks for support,

Rana

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703