Text to Columns tool - Bug/Oversight/or as intended?

[GavinAttard]

Hi 

So i have been using the text to columns tool to parse out a string with a delimiter of 3 characters in the following sequence -^-

However the tool only takes the first - as the delimiter. 

I created a workaround using the regex replace tool, but rather curious as to why this didn't work in the first place. 

Settingsresult

[DataBlender · Accepted solution]

Hi @GavinAttard

The tool only accepts single-character delimiters so the results are 'as intended'.

Using regex is often the workaround I have seen suggested on this forum to those who wish to split on a non-singular character. 

Edit: A quick search (link) highlighted that you can apparently specify a list of delimiters in the text to columns configuration and it will split the field on the occurrence of any one of them as a singular occurrence. However, it doesn't look like that was the case for you since the number of columns was set to 2.

[patrick_digan]

@GavinAttard I have run into the same problem as you before. I do use @DataBlender's regex solution most of the time. I've also gone down more of a brute force route and used a formula tool to replace your many characters with a single character like so:

Replace([Field],"-^-","¥")

Then your text to columns can use the ¥ as a delimiter. I chose the yen symbol since I'm in the US and my data should never contain that symbol. You can use Microsoft word and choose insert>> Symbol and pick a symbol that won't exist in your data if the yen won't work.