Multi-Field Text to Columns (Rows)

Hi Everyone

I have a dataset where the data in multiple fields contains delimiters; see below.

Name	ID	Record ID
John Smith\|John Smith	1\|23	1\|2
Jane Smith\|Jane Smith	2\|50	3\|4

Anyone have any thoughts how to split all the fields into multiple rows? Please see below for the output I am trying to achieve.

Name	ID	Record ID
John Smith	1	1
John Smith	23	2
Jane Smith	2	3
Jane Smith	50	4

Please let me know. Thank you!

Regex

Parse

Preparation

Accepted answers

Joe_Mako

How about the attached?

The workflow will add a ReocrdID (uniquely identify source rows), Transpose to a Name-Value pair for every cell, add a NameID (uniquely identify fields), Summarize Group by NameID,Name for use in renaming the fields later, split the Value field on the | deliminator into rows, add a ValueID (uniquely identify each value and sequence in a record's cell), cross tab into normal structure, deselect unnecessary field, and rename fields.

You might notice that I do not let the Cross Tab tool use the Name field to name the fields. This is because it will change the order of the fields to alphabetical and it changes the field names, replacing some characters with an underscore.

Split.yxmd

All comments

Joe_Mako

How about the attached?

Split.yxmd

zpruitt

Hey, Joe

I made a slight change to your answer, only because in my case it was staggering the rows (all of the data of the first column would populate, with the remaining columns showing as null until the first column finished). To fix this I edited the 2nd formula (ValueID: [Row-1:ValueID]+1) to IF [Row-1:Name]==[Name] THEN [Row-1:ValueID] +1 ELSE 1 ENDIF

amc1794

Hi Joe_Mako,

Is there any way to make this work when there are other additional columns of data which you want to be duplicated in the additional rows which have been inserted? I have adjusted EYamada's example below to explain.

Before

Name	ID	Record ID	City	Country
John Smith\|John Smith	1\|23	1\|2	New York	USA
Jane Smith\|Jane Smith	2\|50	3\|4	London	UK

After

Name	ID	Record ID	City	Country
John Smith	1	1	New York	USA
John Smith	23	2	New York	USA
Jane Smith	2	3	London	UK
Jane Smith	50	4	London	UK

Really appreciate your help.

Tanujit

Awesome! many thanks for this solution. It helped me in similar kind of case.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15874

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703