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!
Solved! Go to Solution.
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:
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!
Thank you Tony, I couldn't make it work, I will definitely go through the videos and learn regex. Thanks for your time!
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!
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
Thank you! That worked. Appreciate your help and time!