Hi All, I have a total duration column in Excel with values like these TotalDuration1h23m17s46m9s29s I need to separate the hours, minutes, seconds, and also get total duration in seconds. As you can see the format and delimiters vary, and there is no consistent length. So, a simple text to columns functions wouldn't work. So I came with below formulae. B2 is the cell the value is present in. For extracting hourIF(ISNUMBER(SEARCH("h",B2)),LEFT(B2,FIND("h",B2)-1),0)For extracting minutesIF(ISNUMBER(SEARCH("h",B2)),MID(LEFT(B2,FIND("m",B2)-1),FIND("h",B2)+1,LEN(B2)),IF(ISNUMBER(SEARCH("m",B2)),LEFT(B2,FIND("m",B2)-1),0))For extracting secondsIF(ISNUMBER(SEARCH("m",B2)),MID(LEFT(B2,FIND("s",B2)-1),FIND("m",B2)+1,LEN(B2)),IF(ISNUMBER(SEARCH("s",B2)),LEFT(B2,FIND("s",B2)-1),0))Total Duration in Seconds(IF(ISNUMBER(SEARCH("h",B3)),LEFT(B3,FIND("h",B3)-1),0))*3600+(IF(ISNUMBER(SEARCH("h",B3)),MID(LEFT(B3,FIND("m",B3)-1),FIND("h",B3)+1,LEN(B3)),IF(ISNUMBER(SEARCH("m",B3)),LEFT(B3,FIND("m",B3)-1),0)))*60+IF(ISNUMBER(SEARCH("m",B3)),MID(LEFT(B3,FIND("s",B3)-1),FIND("m",B3)+1,LEN(B3)),IF(ISNUMBER(SEARCH("s",B3)),LEFT(B3,FIND("s",B3)-1),0)) The above formulae check for values, adapt to varying delimiters, extract them if present or put in 0 if they don't. I have about 7 columns like these, and would like to use alteryx to automate this but learned the excel formula doesn't translate to Alteryx as is. Could anybody help with the best way in going about achieving my end goal? Wasn't sure if using the formula is the best way or if I have to look at regex. Eitherway, I would be new to both options. Any help would be appreciated! Thank you!

Help Converting Excel Formula to Alteryx Formula or Regex

seheri

Hi All,

I have a total duration column in Excel with values like these

TotalDuration

1h23m17s

46m9s

29s

I need to separate the hours, minutes, seconds, and also get total duration in seconds. As you can see the format and delimiters vary, and there is no consistent length. So, a simple text to columns functions wouldn't work.

So I came with below formulae. B2 is the cell the value is present in.

For extracting hour	IF(ISNUMBER(SEARCH("h",B2)),LEFT(B2,FIND("h",B2)-1),0)
For extracting minutes	IF(ISNUMBER(SEARCH("h",B2)),MID(LEFT(B2,FIND("m",B2)-1),FIND("h",B2)+1,LEN(B2)),IF(ISNUMBER(SEARCH("m",B2)),LEFT(B2,FIND("m",B2)-1),0))
For extracting seconds	IF(ISNUMBER(SEARCH("m",B2)),MID(LEFT(B2,FIND("s",B2)-1),FIND("m",B2)+1,LEN(B2)),IF(ISNUMBER(SEARCH("s",B2)),LEFT(B2,FIND("s",B2)-1),0))
Total Duration in Seconds	(IF(ISNUMBER(SEARCH("h",B3)),LEFT(B3,FIND("h",B3)-1),0))3600+(IF(ISNUMBER(SEARCH("h",B3)),MID(LEFT(B3,FIND("m",B3)-1),FIND("h",B3)+1,LEN(B3)),IF(ISNUMBER(SEARCH("m",B3)),LEFT(B3,FIND("m",B3)-1),0)))60+IF(ISNUMBER(SEARCH("m",B3)),MID(LEFT(B3,FIND("s",B3)-1),FIND("m",B3)+1,LEN(B3)),IF(ISNUMBER(SEARCH("s",B3)),LEFT(B3,FIND("s",B3)-1),0))

The above formulae check for values, adapt to varying delimiters, extract them if present or put in 0 if they don't.

I have about 7 columns like these, and would like to use alteryx to automate this but learned the excel formula doesn't translate to Alteryx as is.

Could anybody help with the best way in going about achieving my end goal? Wasn't sure if using the formula is the best way or if I have to look at regex. Eitherway, I would be new to both options. Any help would be appreciated!

Thank you!

Preparation

Accepted answers

jrgo

@seheri

This should fix that. You could also put a select tool before the Formula tool to change the field types to one of the STRING types which should also fix.

IF REGEX_MATCH(TOSTRING([TotalDuration]), '^(\d+)h(?:\d+m)?(?:\d+s)?$')
THEN TONUMBER(REGEX_REPLACE(TOSTRING([TotalDuration]), '^(\d+)h(?:\d+m)?(?:\d+s)?$', '$1')) * 3600
ELSE 0
ENDIF
+
IF REGEX_MATCH(TOSTRING([TotalDuration]), '^(?:\d+h)?(\d+)m(?:\d+s)?$')
THEN TONUMBER(REGEX_REPLACE(TOSTRING([TotalDuration]), '^(?:\d+h)?(\d+)m(?:\d+s)?$', '$1')) * 60
ELSE 0
ENDIF
+
IF REGEX_MATCH(TOSTRING([TotalDuration]), '^(?:\d+h)?(?:\d+m)?(\d+)s$')
THEN TONUMBER(REGEX_REPLACE(TOSTRING([TotalDuration]), '^(?:\d+h)?(?:\d+m)?(\d+)s$', '$1'))
ELSE 0
ENDIF

All comments

TonyA

How about this?

EDIT: I made two changes from the original workflow - converted values to numeric and replaces nulls with zeroes.

ParseDuration.yxmd

TonyA

I missed the comment about also wanting duration in seconds. You can easily do that with a formula tool. There are simple ways to extend the REGEX to support days, weeks, or even just any arbitrary single character or multiple characters. Take a look at some of the resources in the Academy section to learn more. If you're totally new to REGEX, I'd start with the interactive lessons here:

https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons/label-name/Parsing%20Data

jrgo

Hi @seheri

Here's another option that may help.

Expression:

IF REGEX_MATCH([TotalDuration], '^(\d+)h(?:\d+m)?(?:\d+s)?$')
THEN TONUMBER(REGEX_REPLACE([TotalDuration], '^(\d+)h(?:\d+m)?(?:\d+s)?$', '$1')) * 3600
ELSE 0
ENDIF
+
IF REGEX_MATCH([TotalDuration], '^(?:\d+h)?(\d+)m(?:\d+s)?$')
THEN TONUMBER(REGEX_REPLACE([TotalDuration], '^(?:\d+h)?(\d+)m(?:\d+s)?$', '$1')) * 60
ELSE 0
ENDIF
+
IF REGEX_MATCH([TotalDuration], '^(?:\d+h)?(?:\d+m)?(\d+)s$')
THEN TONUMBER(REGEX_REPLACE([TotalDuration], '^(?:\d+h)?(?:\d+m)?(\d+)s$', '$1'))
ELSE 0
ENDIF

Using REGEX, it first finds if the time part exist and if it does, extract the numeric values and convert to seconds (for hours and minutes) then + each IF ELSE condition to return the total seconds.

The REGEX pattern does adjust for if any of the time parts don't exist, as shown in the 2 additional times i entered to test.

Hope this helps!

Jimmy
Teknion Data Solutions

seheri

Hi @jrgo

Thank you very much! this works except for one instance, when there is 0 in the cell. I get an error Formula tried to apply string operator to numeric value (REGEX_REPLACE). Some columns are of type V_String, and some are of type Double. I am trying to figure out how to convert them all to V_String to workaround this error. Thanks again!

Quick Links

Unanswered
Community Events
Groups

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703