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

Text to column using the right-most delimiter

matiasb
5 - Atom

I need to split a text into a fixed number of columns but want to keep the extra delimiters at the beginning.

 

For example, using the - delimiter if I got: 123-123-SOMETHING 

I want to get two columns. Column A: 123-123 and column B: SOMETHING

 

Based on another answer I was thinking in a 3-step solution

 

Step 1 - Formula to reverse the string

Step 2 - Split to columns with delimiter, 2 columns and leaving extra columns in last column

Step 3 - Reverse back the two column string.

 

But I was trying to go for a one step solution probably using REGEX or other formulas. 

 

Basically I'm trying to replicate the following function from PowerBI into a single step in Alteryx:

 

matiasb_0-1622728002247.png

 

 

Does anyone have any insight to resolve using an alternate solution?

4 REPLIES 4
Luke_C
17 - Castor

Hi @matiasb 

 

Want to caveat I'm not an regex expert, but the below should work in regex parse mode:

 

(.*)-(.*)

 

Luke_C_0-1622728255934.png

 

atcodedog05
22 - Nova
22 - Nova

Hi @matiasb 

 

The regex to be used in a Regex tool parse mode would be something like this

 

(.*)\-([^\-]+)

 

 

(.*) : first all character before - .\- is used to escape - function

([^\-]+) : all character instead of - . this way it splits from the right most.

 

Workflow:

atcodedog05_0-1622728389150.png

 

Hope this helps 🙂

matiasb
5 - Atom

Thanks. That work great.... guess will have to clean the dust on the REGEX manuals back from when I was learning Unix 😄

 

Appreciate your assistance

apathetichell
18 - Pollux

Fyi - if you wanted to combine your approach into one formula you could use:

trimleft(reversestring(regex_replace(reversestring([test]),"([!-]+).*","$1")),"-")

 

There should be an easier way using no matching characters to "-" and end of line $ - but I'm still working on it.

Labels