Text to Column

[MarkPitcock]

Afternoon All, 

I have a set of data with multiple columns. 2 columns I need to split into rows has a pipe | delimiter. I need to retain the same data in the rows created from the text to column tool. Using the text to column tool on column B works well but when applying the second time on column E this is when I run into problems. 

Below is the input / output that I am trying to achieve 

Any help will be appreciated. 

Example 1
Input
A	B	C	D	E
1	A|B	Start	End	100|101
Output
1	A	Start	End	100
1	B	Start	End	101
Example 2
Input
1	A|B|C	Start	End	100|101|102
Output
1	A	Start	End	100
1	B	Start	End	101
1	C	Start	End	102

[atcodedog05 · Accepted solution]

Hi @MarkPitcock 

Here is how you do it.

1. Use record id tool to set unique key for rows.

2. Using text to column, delimiter as |, and split to rows.

3. Using multi-row formula tool, groupby RecordID, generating group ID. Group id is the position which will be used for mapping.

4. Joining Field 2 & Field 5 on record ID & group ID. This way it matches to row & position.

Hope this helps 🙂

[MarkPitcock]

atcodedog05 - thank you so much , you've solved the problem.