How to fix text that wraps to a new line from an excel report?
- 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
Hello,
The attached is a sample of a report in excel format that writes long text to a new line. I've included an Output worksheet of the desired results. Is this possible in Alteryx? I tried Text To Columns and Make Columns with no luck.
Thank you!
Solved! Go to Solution.
- Labels:
- Help
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
You must have a way to determine which lines should be combined into the same record. I don't see any pattern on the data.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
If you were manually combining the rows, if you can define one or more rules that you would follow, you could write a workflow. But if some of the text is subjective, without any definite rule to follow, there's nothing you can do.
For example, I don't think there's any logic you could define to know that the two rows for group 10 should be combined. Other than hard-coding logic for something like "the word Vendor on a line by itself should be combined with the row above".
Desired Group Number | Original Text |
1 | 2021-AF-INV- INVESTIGATIONS | |
1 | Investigation Activities- 2021 |
2 | 2021-AF-SOX | SOx Activities for - |
2 | 2021 |
3 | Nonworking |
4 | General Admin Time |
5 | Onboarding - On the Job Training |
6 | Training - Non-CPE |
7 | 2020-GLOBAL-SER-GLOBAL RESPONSE |
7 | COMMUNITY FUND | Global Response |
7 | Community Support Fund |
8 | 2021-GLOBAL-LEG |
8 | | Review |
9 | 2021-GLOBAL-OBJ- TEAM MEETINGS | 2021 |
9 | Team Meetings |
10 | 2021-GLOBAL-OPS-SECURITY | 2021 Security |
10 | Vendor |
11 | 2021-GLOBAL-RMS-RMS SUPPORT | RMS |
11 | Support - 2021 |
12 | Nonworking |
Chris
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @ChrisTX,
Good point, I've updated the sample to include additional columns from the report to help with logic. The text wraps when:
- Code begins with a year
- Code contains '|'
- Project > 0
If Project IsNull and Nonworking or Admin >0 then the text doesn't wrap.
- 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
Wow very cool approach, wasn't sure how to account for the 3 lines! I also learned that you can concatenate in the Summarize tool, very helpful!
