Splitting data into columns using a hyphen and spaces as the delimiter

[Amanda_Breeze]

I'm trying to use the Text to Columns tool to split data into columns using a hyphen and spaces " - " as the delimiter.

Trouble is, some of my data contains a hyphen with spaces, some contains just a hyphen, and some contains both.

I only want the the string to be split where there is a hyphen AND spaces. I don't want just the hyphen to be the delimiter.

For example, I have the following string values in my data;

No. DATA

1. Sandgate - Shorncliffe

2. Rothwell - Kippa-Ring

3. Mount Coot-tha

A filter tool configured using Contains([DATA], " - ") prior to the text-to-columns tool filters out #3 fine.

That just leaves #1 and #2

When I use a hyphen as the delimiter for the Text to Columns Tool and set the Number of columns to 2, it splits #1 as one would expect into two columns, which is what I want.

However, it splits #2 like this:

Column1	Column2
Rothwell	Kippa

It's cutting off the Ring of Kippa-Ring

How do I specify that I want it to only split the string where it is separated by a hyphen AND spaces and not just a hyphen? 

Note I've tried using REGEX but I'm not overly familiar with it so if someone can tell me how to use REGEX_Match in my initial filter, that might work.

Thanks

Amanda

[fmvizcaino · Accepted solution]

Hi @Amanda_Breeze ,

Attached is an example showing how to get that done by using a formula tool and a text to columns. You can also use the parse function from regex tool.

Let me know if that works for you.

Best,

Fernando Vizcaino

[Amanda_Breeze]

Haha I knew the solution would be simple. Here I am trying to split on the hyphen as is when all I needed to do was replace it with something else. A typical case of not seeing the forest for the trees.

Thank you Fernando!