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

Split Data into Two Separate Fields

Chirag_Gandhi07
8 - Asteroid

Hi,

 

I  have the following data:

John - Smith - Dave

ABC - DEF - GHI

 

How can I split this data into two fields so that it outputs:

Name 1                             Name 2

John - Smith                     Dave

 

I tried to use Text to Columns tool but because these values have multiple dashes, it doesn't work. Any help would be much appreciated. Thank you!

 

6 REPLIES 6
BenMoss
ACE Emeritus
ACE Emeritus

So you want to split on the 2nd instance of the character rather than the first?

 

Simple method, use the text to columns to split to three fields, and then use a formula to concat split 1 and 2 back together.

 

Option B, use Regex, place it in parse mode and use the following statement...

(\w+ - \w+ )-(.+)

Ben

Kenda
16 - Nebula
16 - Nebula

Hey @Chirag_Gandhi07 !

 

I have a suggestion for you that only involves using the Formula tool. 

 

First, create a new field with this expression:

REGEX_Replace([Field1], "(.*) - (.*)", "$2")

Next, modify your original field with this expression:

REGEX_Replace([Field1], "(.*) - (.*)", "$1")

It is important that you put these in this order in your Formula tool. Basically, the new field is being created by taking anything after the last dash from your original field. The second expression is modifying the original field, then, to only keep anything before the last dash.

 

Hope this helps!

Chirag_Gandhi07
8 - Asteroid

@Kenda @BenMoss Thank you for your responses. I should have been more clear. My apologies. Some values have more than two dashes. What I would like to be able to do is to split the values into two fields and have it split so the word after the final dash is split into a separate field. Hope that makes sense

Kenda
16 - Nebula
16 - Nebula

@Chirag_Gandhi07  Did you try my option? Based on your additional detail, it sounds like exactly what you're looking for still.

Chirag_Gandhi07
8 - Asteroid

@Kenda It worked! Thank you so much!

BenMoss
ACE Emeritus
ACE Emeritus
I see!

In addition to BarnesK's solution an alternative would be the following...

Use the reversestring function to flip the text, then split on the first delimiter, then use the reversestring function to flip your new fields into the focus direction.

Ben
Labels