Text to column - apply to only one instance of a delimiter OR apply it to every nth column

[erinylai]

Hi all, I am trying to work around an instance where the text to column can apply to the indicated delimiter under certain instances. 

The following shows the raw data set, what I have been able to build so far, and what I am hoping to achieve. 

So I am hoping to achieve a final output with 4-column groups covering the area code, connection number, the beginning of the range, and the end of the range. Wherever the "range" is initially a number, I would like to show it as number - number so it covers both columns. 

Here is my current workflow (not sure how helpful it may be without seeing the file...happy to provide screenshots of the workflow though!) - basically the order is to

(1) turn numbers into ranges and text to column based on "+"

(2) apply "text to column" to multiple columns based on three delimiters: "-", ",". and space

What I am running into is that, because the connection column has a "-" in some fields, the text to column step also applies to this column and adds an additional step. I am wondering if there is a way to avoid touching the hyphenated item in the Connection column. 

One method I could think of is to text to column based on "+", ",", and space after converting numbers into ranges, which leaves me with the Area, Connection, and Range. I can then apply text to column again on every 3rd column which is the Range column. Does anyone know how to do that?

Would appreciate any pointers. Thank you!! 

[SPetrie · Accepted solution]

Something to consider is regex.

Im not the best with it so maybe someone else can improve on this, but here is one method to accomplish this.

Do a formula tool with regex_replace and have it look for a comma followed by atleast one number and a dash. Then replace that with the same thing but substitute ~ instead of the dash.

REGEX_Replace([string],",(\d+)(-)",",$1~")

Then you can pass the string to the text to columns and use \s,+~ as the delimiters and it should split they way you are wanting.

[erinylai]

This was really helpful!!! Thank you so much SPetrie!!! I literally spent 5hr trying to crack this yesterday.