Splitting Text to 2 Columns with variable number of delimiters

Question

Hi,

I have a column that looks like this:

ABC - Delhi

Mumbai

DEF - Kolkata - Apple

GHI - Chennai-Ball

And I want the output to be like the following:

[Column 1]      [Column 2]

ABC                Delhi

Mumbai

DEF                 Kolkata - Apple

GHI                  Chennai-Ball

So, if there's a dash I want all the text before the first dash to go into Column 1 and the remaining goes into Column 2, and if there's no dash, then all text goes into Column 2.

Please guide!

Thanks.

AngelosPachis · Accepted Answer

Hi @Adityasa ,

Maybe try this workflow instead? Same logic as in the previous thread, but I have added a formula tool to move values if there is no dash.

Let me know of that worked for you.

Regards,

Angelos

atcodedog05 · Accepted Answer

Hi @Adityasa

Here is way you can achieve this.

I am preping the data so that when it doesnt contain '-' add '-' at the begin. And replacing first occurrence of  ' - ' to '-' (no spaces) so that it easier to split. After that i am using Text to column to split into 2 columns

Hope this helps 🙂