Spilt cell into row
- 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
I am trying to get from the below table to the output table given just below. I have tried the Text to column function using \n as the delimiter to have each line in a separate row but that didn't work to start with. Any ideas? Many thanks in advance
Name | Category |
Name 1 | • X-555555 - sdfsdfsdfssdf sdfsdfsdf sdfsdfsd - sdfsdf&sdfsdf • X-444444 - sdf sdfsdf -sdfsdf-ewrwer • X-333333 - sdfsd sdfsdf8sdfsf sdfsdfs -sdf • X-111222 - sdfsdfsdf sdfs • 12345 - sdfsd sdfsdf bb rr - dsdf |
Name 2 | • 567890 - stthj khj 78 ztu -llf |
Name 3 | X-223366 - sdfsfsdf sdfsf sdfsdf |
Name 4 | • 33001 -sdsdf sdfsdf fff • 33662 - sdfsdf • B-668877 - sdfsdfsdfbsfsdf -sdfs |
Name | New Column |
Name 1 | X-555555 |
Name 1 | X-444444 |
Name 1 | X-333333 |
Name 1 | X-111222 |
Name 1 | 62266 |
Name 2 | 567890 |
Name 3 | X-223366 |
Name 4 | 33001 |
Name 4 | 33662 |
Name 4 | B-668877 |
Solved! Go to Solution.
- Labels:
- Preparation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @SylviaK I mocked up a workflow let me know what you think?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
That is the correct idea - you'd want to use the text to columns with the option selected to make new rows. After you have a new line for each item, you'd want to use another text to columns to just grab the first piece. You could use a space as the delimiter.
I'm not quite sure I follow the logic in your example though - where is Name 1 and value 62266 coming from in your output?
Are you able to upload sample data to work with? I'm having issues copying the formatting from what you originally posted.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks echuong1. My bad, Name 1 and value 62266 was an error. Pls find attached a file with the sample data
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thx Joseph. Your solution works well. Unfortunately in the original data "." is not always used as a delimiter between lines. sometimes we just have a line break, that's all.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
With the file you shared, a majority of the records are already coming in as separate new lines. I did notice there were two (records for name 3) that came in in the same cell. I used text to columns with a delimiter on "\n" to separate these into separate rows. From there, I removed the bullet point mark at the beginning in a Formula tool, and then Regex to pull out the first part containing digits.
Let me know if this works for you!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks so much echuong1. unfortunately I can't open the workflow you sent. This error message pops up. do you know how to fix this?
There was an error opening "C:\Users\hueglisy\Downloads\New Workflow1 (2)\New Workflow1.yxmd":
This workflow was created by a more recent version of Alteryx, and may contain tools or functionality not present in this version. Alteryx does not support using an earlier version of Alteryx to open a workflow created with a newer version. For best results, download the latest version of Alteryx.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Also when I try to use the "/n" as a delimiter, it doesn't work. I get several lines for US-OBL-2503 instead of 2 lines 2074 & 3533. Would you know why that is? Many thanks in advance
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
The error is because I have a newer version of Designer installed. The workflow I uploaded is a packaged workflow. Upon importing it should have created a folder with the actual .yxmd file in it. Open the .yxmd file with a text editor and change the version to the one that you have.
With regard to the delimiter in your example, it should be "\n" not "/n."
