Split Data into Two Separate Fields

Chirag_Gandhi07

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!

Workflow

Accepted answers

Kenda

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!

All comments

BenMoss

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

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")

Hope this helps!

Chirag_Gandhi07

@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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703