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.
Settings
result
Solved! Go to 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.
@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.