Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Help Converting Excel Formula to Alteryx Formula or Regex

seheri
5 - Atom

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 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!

7 REPLIES 7
TonyA
Alteryx Alumni (Retired)

How about this?

 

 

Workflow.png

Output.png

 

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

TonyA
Alteryx Alumni (Retired)

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%20...

jrgo
14 - Magnetar

Hi @seheri 

 

Here's another option that may help.

image.png

 

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
5 - Atom

Thank you Tony, I couldn't make it work, I will definitely go through the videos and learn regex. Thanks for your time!

seheri
5 - Atom

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!

jrgo
14 - Magnetar

@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

 

seheri
5 - Atom

Thank you! That worked. Appreciate your help and time!

Labels