Alteryx Designer Desktop Discussions

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

If then formula - turn a number (e.g., 2) into a range (following the example: "2-2")

7 - Meteor

Hi all, I am trying to do the following for the data shown: wherever there is a number instead of a range, I want to turn that number into a range. For example, "1" gets turned into "1-1" and "2" gets turned into "2-2". 


Current View:



Desired View



If anyone has any pointers I would really appreciate it! Thank you. 



Hello @erinylai 


You can achieve the desired output in many ways; attached are 2 


1- One that uses Regex.



2- One that check for hyphen.



Hope that helps. Cheers!

7 - Meteor

Wow Christine thank you so much!! That was super helpful and thanks so much for including the workflow. 


I have a follow-up question as I tried things out on my end. The screenshot I showed in the message was more of a intermediary step and as I went back to the initial view I found it difficult to incorporate the items. I have attached as follows the initial view (raw data set), current view (where things stand after I parse it based on the "+" character), and the final desired view. Could you please help me out? Thank you so much!




Hi @erinylai 


Please see attached. It's exactly the same methods, I just included the extra columns.






7 - Meteor

Thank you so much Christine! I tried it out on my end and one thing I am running into in the if contains formula is that the formula is only picking up one digit of the numbers after "-". In the grand data sets, sometimes the numbers could go into 3 or 4 digits. Could you please help me with the formula such that it could also turn "20" into "20-20" and "138" into "138-138" for example? 


Attached the data set for your convenience. Thank you a ton!!!


7 - Meteor

I actually think I just figured it out! 


I replaced the IF Contains formula as follows and changed the "1" from the previous expression to the one bolded below and the formula would work out regardless of the number of characters after "," - 

if !contains([_CurrentField_],"-") then [_CurrentField_]+"-"+Right([_CurrentField_], FindString(ReverseString([_CurrentField_]), ",")) else [_CurrentField_] endif


Thank you again Christine!