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

[erinylai]

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. 

[christine_assaad]

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!

[erinylai]

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!

[christine_assaad · Accepted solution]

Hi @erinylai 

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

Output:

[erinylai]

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

A,123	BRE,32-38
FRE,1500	G,44
R,225	RX,55-149

[erinylai]

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!