Split value in one cell into muti-rows

[k3pineapple]

Hi All,

I am stuck on a data cleansing progress flow.

Need your help to build a flow to split the value in one cell, every cell has dirty value need to be reformatting. Thx

Input

ID	Name	Account1	Account2
1	ABC	12345	{12345}
2	CDE	34567	{123 456 78} {34567}
3	FGH	78901	{789 01 237^78901}
4	IJK	23456	23456
5	LMN	345 678 90	{345 678 90}

Output

ID	Name	Account1	Account2
1	ABC	12345	12345
2	CDE	34567	123 456 78
2	CDE	34567	34567
3	FGH	78901	789 01 237^78901
4	IJK	23456	23456
5	LMN	345 678 90	345 678 90

[DataNath]

Hey @k3pineapple - how does something like this look? We use the } as a delimiter to split to rows on in the Text-To-Columns tool. Following that, we simply remove { and trim to remove additional whitespace. Seems to get what you're after but please do let us know if this doesn't quite fit your actual data.

[FinnCharlton · Accepted solution]

Hi @k3pineapple , you can do this with the RegEx tokenise tool, and clean the data with a RegEx Replace function:

The RegEx expression looks intimidating, but it is just telling the tool to split anything within { } onto a new line, also accounting for digits not it { }.

[flying008 · Accepted solution]

Hi, @k3pineapple 

Maybe you can use RegEx tool by Tokenize method:

Spoiler

([\d\s\^]+)(?>\}|$)