text to column by multiple characters as single delimiter

[Hangxing_Zhou]

I have a long formatted text, need to split them for separate columns, the delimiter is not a single character, but includes multiple like {"moduleid"

Does current "text to column" recognize the multiple character as a single delimiter?

[Hangxing_Zhou]

I worked out a solution， thanks

[clmc9601 · Accepted solution]

Hi @Hangxing_Zhou,

The texttocolumns tool will treat every character as a separate delimiter. I can see at least two workarounds to this, though!

The first and simpler solution is to use a formula tool before the texttocolumns tool. Use the regex_replace function to replace your multi-character delimiter with a single character delimiter. For example:

regex_replace([column], '\{"moduleid"', '|')

Then use the | delimiter in the texttocolumns tool.

The second solution is to use the regex tool on the tokenize option. I'd only recommend this if you're comfortable with regex or willing to spend a decent amount of time troubleshooting it haha. I would write you an example expression, except this is the regex option where I am currently least proficient.

* Edit-- I just saw your above post too late, but I hope this is helpful for the future.

[Hangxing_Zhou]

Yes, it is. I just used the replace function as well. Thanks a lot!