Multiple delimiter characters

[Selrac]

Just a beginner question

Is it possible to have multiple characters as a delimiter in the text to column? For example, using the tutorialData, can I split the Gender using '//' somehow? It seems like it is just using '/' instead of '/' 

[JosephSerpis · Accepted solution]

Hi @Selrac you can split the column into 3 if you make some additional changes in the Text to Column configuration. The first change the delimiter to only one / in the Extra character option select Drop with warning and in advanced options select Skip empty columns. I mocked up a workflow to showcase this.

[ImadZidan]

Hello @Selrac ,

Answering your question. you can use multiple delimiters. However, they are evaluated as an or not and.

So // means / or /. the second part us that yo can not delimit on a word.

This is to raise awareness on the tool. @JosephSerpis  has provided a good solution.

I hope it helps

[Selrac]

Thanks @JosephSerpis, very clever.

[carlospelay]

Thanks - this was super helpful!

[Hiblet]

If you have to split on multiple characters, you can do this with two steps;  First, use a Formula tool to call the string Replace() function, and swap your multiple character delimiter for a single extended ASCII character.  You have to choose an ASCII char that is definitely not in your strings.  Here I use the degree symbol to replace the combination delimiter SemiColon-Space...

    Replace([Field1],"; ","°")

Then you can connect a Text-to-Columns tool, and use the degree symbol as your single character delimiter.

You can get the extended characters using the Windows CharMap app (distributed within Windows itself).  Start, Run, "charmap".  I arbitrarily chose the degree symbol, because I was expecting text only.  You have to choose a character that is within the first 255 characters, as the Text-to-Columns tool seems to only work with these, and not the full unicode character set.

My use-case was processing HTTP header data, where Cookie variables are separated by a semi-colon followed by a space.