How to fix text that wraps to a new line from an excel report?

[joesparty]

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!

[gabrielvilella]

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. 

[ChrisTX]

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

[joesparty]

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.

[gabrielvilella · Accepted solution]

Hi @joesparty, see if this is what you are looking for:

[joesparty]

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!