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!
Solved! Go to Solution.
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
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!
@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
@Chirag_Gandhi07 Did you try my option? Based on your additional detail, it sounds like exactly what you're looking for still.
@Kenda It worked! Thank you so much!