Text To Column - Help

[Shundip]

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

[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 · Accepted solution]

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.

[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

[OllieClarke]

Hi @Shundip here's the workflow in your version

[Shundip]

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