Alteryx Designer

Find answers, ask questions, and share expertise about Alteryx Designer.
Register for the upcoming Live Community Q&A Session - and don't forget to submit your questions for @DeanS regarding the future role of analytics here.
SOLVED

Split a string field into two fields using RegEx / Text To Columns

Highlighted
7 - Meteor

Hi,

 

I want to split the following string field into 2 (by separating the number and words on the first instance only). See the following data:

 

4759-05-71 AB(CD)

1090-43-000-000Petty Cash:Admin

1990-43-000-000Petty Cash: Admin - #4201:2Admin

1077-00-00 HDK Concentration (Ending 3873)

 

As you can see there aren't any set structure to the string. But I would like the following:

 

Column 1                                     Column 2

4759-05-71                                  AB(CD)

1090-43-000-000                         Petty Cash:Admin

1990-43-000-000                         Petty Cash: Admin - #4201:2Admin

1077-00-00                                  HDK Concentration (Ending 3873)

 

Thank you so much for your help in advance.

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner
Try this formula:

Regex_replace([field],"([0-9\-]+)(.*)",'$1|$2')

You can then parse it. If you're familiar wit he the regex tool you could use that and save a tool.

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
13 - Pulsar

Here is the sample workflow for your case.  Hope this is helpful. 

 

InputInputWorkflowWorkflowResultsResults

Highlighted
7 - Meteor

@MarqueeCrew,

 

Thank you. Your Regex formula works perfectly. What will be a good website that you recommend to learn regex expression? It's such a powerful tool. 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner
Regex101.com

Try also YouTube with Alteryx and Regex. You might like some of my Marqueecrew content and want to subscribe.

Cheers,

Mark
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
13 - Pulsar

@calvinwong, I am curious to know if you tried my solution-a length workflow though 

Highlighted
7 - Meteor

@ponraj,

 

Thank you for your help. I did look at your workflow and it did the work as well (just in a longer way). :) 

Labels