Concat string & create new row

Question

I want to search for "Fat" in each row then creating new role for strings that come after "Fat" (if any) by maintaining inputs in other columns the same

Input file:

NameDateList of actionsMister A9/6/2022Bat, Hat, Rat, Fat, Hat, Rat, OatMister A10/12/2022Rat, Oat, Rat, Fat, That, RatMister B2/8/2022Bat, Oat, RatMister C4/5/2022That, Rat, Fat, Bat, Bat, Hat, Eat

Output:

NameDateList of actionsMister A9/6/2022Bat, Hat, Rat, FatMister A9/6/2022Hat, Rat, OatMister A10/12/2022Rat, Oat, Rat, FatMister A10/12/2022That, RatMister B2/8/2022Bat, Oat, RatMister C4/5/2022That, Rat, FatMister C4/5/2022Bat, Bat, Hat, Eat

If there is more than one Fat in input row, keep splitting until no "Fat" left in ending row

Appreciated all the helps

grazitti_sapna · Answer

@123love55448 , my solution is similar to @JosephSerpis , you can replace the text Fat, with Fat| or some other delimiter instead of , and then split the data to rows using text to columns tool on the basis of |. Attaching the solution hope it helps.

Spilt_Fat.yxmd

JosephSerpis · Answer

Hi @123love55448 I came up with an approach of using a formula to replace fat, with fat| so I could use | as delimiter in the Text to columns tool to achieve the output you stated.

Spilt_06092022.yxmd

IraWatt · Answer

If you are wanting to keep the original row here is one way:

I split each row to individual actions and use a multirow formula to generate the new text for each row.

If you want to learn more about the Multi-Row-Formula Tool the community has some quick and easy videos on the topic here: https://community.alteryx.com/t5/Interactive-Lessons/Multi-Row-Formula/ta-p/82872

Any questions or issues please ask

Ira Watt
Technical Consultant
Watt@Bulien.com

Workflow.yxmd