Text To Column - Help
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Solved! Go to Solution.
- Labels:
- Custom Tools
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @Shundip here's the workflow in your version
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you for this and sharing a compatible version @OllieClarke - it worked.
![](/skins/images/03E14B015E5210679CFB65D9FFC7B381/responsive_peak/images/icon_anonymous_message.png)