Text To Column - Help

Hi There,

Hoping someone can help.

Im trying to split the information in the roles cell into multiple columns using the text to columns function, but the issue is there isnt a common delimiter. E,g it could be a close bracket but some rows dont have any.

An example of the sheet is attached.

One person could have upto 8 of these roles so I need to be able to view all of them in a separate column.

In Excel if I was using text to columns, I would use Ctrl J in the delimiter box so that it looks like the cursor is blinking and this would work by splitting this out into multiple roles.

Can anyone suggest a something or do I need to split this in the Excel file to begin with?

Kindest Regards

Roles.xlsx

Custom Tools

Accepted answers

OllieClarke

Adding on to @fmvizcaino's answer, in your case you have empty lines separating the roles. If you set up the text to columns tool like this:

then you can ignore these. You will have to split to 1 more column than you need, or else it won't skip the final empty row. but you can drop this column with a select tool.

If you have an unknown number of roles, then you can split to rows rather than columns, as this doesn't require a specified number of columns. You can then add a 'column id' field with a multi-row formula and then cross-tab the data back out to columns, safe in the knowledge that you haven't missed a record that has 9 roles instead of 8.

to cols.yxzp

All comments

fmvizcaino

Hi @Shundip ,

You can still use text to columns tool and use new line as delimiter \n

Let me know if that works for you.

Best,

Fernando Vizcaino

OllieClarke

Adding on to @fmvizcaino's answer, in your case you have empty lines separating the roles. If you set up the text to columns tool like this:

then you can ignore these. You will have to split to 1 more column than you need, or else it won't skip the final empty row. but you can drop this column with a select tool.

to cols.yxzp

Shundip

Hi Fernando,

Thank you for that - it has worked but I am getting a message in the original Roles column to say "this cell has embedded new lines" - there is a red triangle in the corner of the cell. Does this mean there is data I cant see?

Kind Regards

Shundip

Thank you for this @OllieClarke - I cant open the workflow as unfortunately using version 2018.4.3.54046. but Im going to try what you have suggested below to see how I get on.

Kind Regards

Shundip

Thank you for this and sharing a compatible version @OllieClarke - it worked.

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

michael_weaver 335

Hollingsworth 335