Clean up and reformatt large data set

[Estee28]

Hello,

I was wondering if I could get some help please.

I am attempting to reformat a large set of data to load into qlikview. At the moment there are multiple columns with values on one line separated by a comma. I would like to find a way to separate the data for all of the columns. I have included a small sample below.

[TABLE="width: 2154"]
<colgroup><col span="2"><col><col><col><col><col><col><col span="2"><col><col span="5"><col></colgroup><tbody>[TR]
[TD]Full Name (Member)[/TD]
[TD]Call Lead[/TD]
[TD] Name[/TD]
[TD]Product Group[/TD]
[TD]Product Pitch Category[/TD]
[TD]Call Method[/TD]
[TD]Date & Time of Call[/TD]
[TD]Call Purpose[/TD]
[TD]Call Purpose2[/TD]
[TD]Subject[/TD]
[TD]Summary Of Call[/TD]
[TD]Rel ID[/TD]
[TD]CPB Lead GEID[/TD]
[TD]Deployment Region[/TD]
[TD]Lead Banker (Relationship Name)[/TD]
[TD]LeadBanker ID[/TD]
[TD]CPB Employee IDs[/TD]
[/TR]
[TR]
[TD]Name 1[/TD]
[TD]Lead 1[/TD]
[TD]Name 1, Name 2, Name 3, Name 4, Name 5, Name 6[/TD]
[TD]Product 1, Product 2, Product 3, Product 4[/TD]
[TD]Pitch 1, Pitch 2, Pitch 3[/TD]
[TD]Phone[/TD]
[TD="align: right"]11/1/2017[/TD]
[TD]purpose 1, purpose 2, purpose 3[/TD]
[TD]Purpose 1[/TD]
[TD]Subject 1[/TD]
[TD]Summary 1[/TD]
[TD]ID 1[/TD]
[TD="align: right"]100001[/TD]
[TD]Region 1[/TD]
[TD]Banker 1[/TD]
[TD="align: right"]100011[/TD]
[TD]100011, 100012, 100013, 100011, 100012, 100013[/TD]
[/TR]
[TR]
[TD]Name 2 [/TD]
[TD]Lead 2[/TD]
[TD]Name 1[/TD]
[TD]Product 1[/TD]
[TD]Pitch 1, Pitch 2, Pitch 4[/TD]
[TD]Person[/TD]
[TD="align: right"]11/2/2017[/TD]
[TD]purpose 1[/TD]
[TD]Purpose 2[/TD]
[TD]Subject 2[/TD]
[TD]Summary 2[/TD]
[TD]ID 2[/TD]
[TD="align: right"]100002[/TD]
[TD]Region 2[/TD]
[TD]Banker 2[/TD]
[TD="align: right"]100012[/TD]
[TD="align: right"]100011[/TD]
[/TR]
[TR]
[TD]Name 3[/TD]
[TD]Lead 3[/TD]
[TD]Name 1, Name 2, Name 3[/TD]
[TD]Product 1[/TD]
[TD]Pitch 1[/TD]
[TD]Phone[/TD]
[TD="align: right"]11/3/2017[/TD]
[TD]purpose 1[/TD]
[TD]Purpose 3[/TD]
[TD]Subject 3[/TD]
[TD]Summary 3[/TD]
[TD]ID 3[/TD]
[TD="align: right"]100003[/TD]
[TD]Region 3[/TD]
[TD]Banker 3[/TD]
[TD="align: right"]100013[/TD]
[TD]100011, 100012, 100015[/TD]
[/TR]
[TR]
[TD]Name 4[/TD]
[TD]Lead 4[/TD]
[TD]Name 1[/TD]
[TD]Product 1, Product 2, Product 3, Product 7[/TD]
[TD]Pitch 1[/TD]
[TD]Phone[/TD]
[TD="align: right"]11/4/2017[/TD]
[TD]purpose 1, purpose 5[/TD]
[TD]Purpose 4[/TD]
[TD]Subject 4[/TD]
[TD]Summary 4[/TD]
[TD]ID 4[/TD]
[TD="align: right"]100003[/TD]
[TD]Region 4[/TD]
[TD]Banker 4[/TD]
[TD="align: right"]100011[/TD]
[TD="align: right"]100011[/TD]
[/TR]
[TR]
[TD]Name 5[/TD]
[TD]Lead 5[/TD]
[TD]Name 1, Name 2, Name 3, Name 4, Name 5, Name 10[/TD]
[TD]Product 1, Product 2[/TD]
[TD]Pitch 1, Pitch 2, Pitch 7[/TD]
[TD]Phone[/TD]
[TD="align: right"]11/5/2017[/TD]
[TD]purpose 1, purpose 6[/TD]
[TD]Purpose 5[/TD]
[TD]Subject 5[/TD]
[TD]Summary 5[/TD]
[TD]ID 5[/TD]
[TD="align: right"]100003[/TD]
[TD]Region 5[/TD]
[TD]Banker 5[/TD]
[TD="align: right"]100011[/TD]
[TD]100011, 100012, 100017, 100012, 100016[/TD]
[/TR]
[TR]
[TD]Name 6[/TD]
[TD]Lead 6[/TD]
[TD]Name 1, Name 2, Name 3, Name 4, Name 5,[/TD]
[TD]Product 1, Product 2[/TD]
[TD]Pitch 1, Pitch 2, Pitch 8[/TD]
[TD]Person[/TD]
[TD="align: right"]11/6/2017[/TD]
[TD]purpose 1, purpose 7[/TD]
[TD]Purpose 6[/TD]
[TD]Subject 6[/TD]
[TD]Summary 6[/TD]
[TD]ID 6[/TD]
[TD="align: right"]100003[/TD]
[TD]Region 6[/TD]
[TD]Banker 6[/TD]
[TD="align: right"]100011[/TD]
[TD]100011, 100012, 100018, 100019[/TD]
[/TR]
[TR]
[TD]Name 7[/TD]
[TD]Lead 7[/TD]
[TD]Name 1, Name 2, Name 3, Name 4, Name 5, Name 12[/TD]
[TD]Product 1, Product 2, Product 3, Product 10[/TD]
[TD]Pitch 1, Pitch 2[/TD]
[TD]Phone[/TD]
[TD="align: right"]11/7/2017[/TD]
[TD]purpose 1[/TD]
[TD]Purpose 7[/TD]
[TD]Subject 7[/TD]
[TD]Summary 7[/TD]
[TD]ID 7[/TD]
[TD="align: right"]100003[/TD]
[TD]Region 7[/TD]
[TD]Banker 7[/TD]
[TD="align: right"]100011[/TD]
[TD]100011, 100012, 100012, 100018, 100019, , 100020[/TD]
[/TR]
[TR]
[TD]Name 8[/TD]
[TD]Lead 8[/TD]
[TD]Name 1[/TD]
[TD]Product 1, Product 2, Product 3, Product 11[/TD]
[TD]Pitch 1, Pitch 2[/TD]
[TD]Person[/TD]
[TD="align: right"]11/8/2017[/TD]
[TD]purpose 1, purpose 9[/TD]
[TD]Purpose 8[/TD]
[TD]Subject 8[/TD]
[TD]Summary 8[/TD]
[TD]ID 8[/TD]
[TD="align: right"]100003[/TD]
[TD]Region 8[/TD]
[TD]Banker 8[/TD]
[TD="align: right"]100011[/TD]
[TD="align: right"]100011[/TD]
[/TR]
[TR]
[TD]Name 9[/TD]
[TD]Lead 9[/TD]
[TD]Name 1[/TD]
[TD]Product 1, Product 2, Product 3[/TD]
[TD]Pitch 1, Pitch 2, Pitch 11[/TD]
[TD]Person[/TD]
[TD="align: right"]11/9/2017[/TD]
[TD]purpose 1, purpose 10, purpose 12[/TD]
[TD]Purpose 9[/TD]
[TD]Subject 9[/TD]
[TD]Summary 9[/TD]
[TD]ID 9[/TD]
[TD="align: right"]100003[/TD]
[TD]Region 9[/TD]
[TD]Banker 9[/TD]
[TD="align: right"]100011[/TD]
[TD="align: right"]100011[/TD]
[/TR]
[TR]
[TD]Name 10[/TD]
[TD]Lead 10[/TD]
[TD]Name 1, Name 2[/TD]
[TD]Product 1[/TD]
[TD]Pitch 1, Pitch 2, Pitch 12[/TD]
[TD]Phone[/TD]
[TD="align: right"]11/10/2017[/TD]
[TD]purpose 1[/TD]
[TD]Purpose 10[/TD]
[TD]Subject 10[/TD]
[TD]Summary 10[/TD]
[TD]ID 10[/TD]
[TD="align: right"]100003[/TD]
[TD]Region 10[/TD]
[TD]Banker 10[/TD]
[TD="align: right"]100011[/TD]
[TD]100011, 100012[/TD]
[/TR]
[TR]
[TD]Name 11[/TD]
[TD]Lead 11[/TD]
[TD]Name 1, Name 2, Name 3, Name 4, Name 5, Name 16[/TD]
[TD]Product 1, Product 2, Product 3, Product 14[/TD]
[TD]Pitch 1[/TD]
[TD]Person[/TD]
[TD="align: right"]11/11/2017[/TD]
[TD]purpose 1, purpose 13[/TD]
[TD]Purpose 11[/TD]
[TD]Subject 11[/TD]
[TD]Summary 11[/TD]
[TD]ID 11[/TD]
[TD="align: right"]100003[/TD]
[TD]Region 11[/TD]
[TD]Banker 11[/TD]
[TD="align: right"]100011[/TD]
[TD]100011, 100012, 100023, 100022, 100012, 100021[/TD]
[/TR]
</tbody>[/TABLE]


Please let me know if you require further information.

Kind regards,

Esther