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

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