Embedded newlines in multiple cells
- 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
My data has multiple cells with embedded newlines in the example below.
I need to split each newline into a separate row so that the output looks like this -
I have tried the Text to Column tool with \n to split into rows and have also tried Regex to replace the \n with a delimiter, then a Text to Column tool to split each of the columns into rows. Both of these methods produce an output with duplicated records.
Any help would be greatly appreciated.
Thanks,
Tony
Solved! Go to Solution.
- Labels:
- Parse
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @Tbuff
Here's an example of this, you're on the right track with text to columns.
- I filtered out the empty records then transposed the data
- Then I did text to rows on the one column containing the values
- I used the tile tool to associate the different fields together
- Crosstabbed the data back
- 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
Hi @Tbuff
Here is my take on it. I am splitting them separately and joining them.
Workflow:
Hope this helps : )
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks for all the help with this! All of your solutions resolved my issue.