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

[calvinwong]

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.

[MarqueeCrew · Accepted solution]

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

[ponraj · Accepted solution]

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

InputWorkflowResults

[calvinwong]

@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. 

[MarqueeCrew]

Regex101.com

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

Cheers,

Mark

[ponraj]

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

[calvinwong]

@ponraj,

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