Delimiter split

Hi,

I was trying to split by a delimiter which is double underscore (__) however, 'Text to columns' is also splitting by single underscore.

Is there a way to have the data only parsed by __ or do I have to change the __ to another character?

Please find the examples in attachment.

Thank you in advance,

Joao

Datasets

Accepted answers

Hi @JBATAIDE you can convert a double underscore to a pipe with this formula:

REPLACE([field],'__','|')

And then use this to split your columns

edit: removed unnecessary regex

I would go with you second option and replace __ with a unique character then split

Hi @JBATAIDE you can convert a double underscore to a pipe with this formula:

REPLACE([field],'__','|')

And then use this to split your columns

edit: removed unnecessary regex

You can also use RegEx to achieve this. See attached for an example.

Use formula tool to replace your specific character from the field.

Formula: Replace([Your field], '__','|')

Then use text to column with pipe '|'.

Thank you, that's a good solution. However, as I had the '__ ' delimiter in several columns I had to use the replace

Kind Regards,

Joao