VBA SPLIT into cells and reshape into table

[johnnyjohn]

Hi Fellas !

I am facing two issues.

1) I am struggling to split a cell that contains multiple lines into arrays.
2) Then, I am trying to shape the arrays into a list-table
[The third step is to create a pivot table from the list, yearly basis (a year starting from the 1st of March).

However, when I apply the Split function, the macro split it once only.

Sub SplitF()

Thisworkbook.Sheet1.Range("C3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.TextToColumns Destination:=Range("C3"), DataType:=xlFixedWidth, TrailingMinusNumbers:=True

End Sub

And when I am trying to make a loop with For x=1 to Len(Range("C3"))
Nothing happen. I have spent two-days on it. So now, I am kindly asking for your help to give me the right direction.

Thanks a lot !

What I have

ISIN

Cash Flow

GB0004893086 CORP

07/12/2019 21250 0 07/06/2020 21250 0 07/12/2020 21250 0 07/06/2021 21250 0 07/12/2021 21250 0 07/06/2022 21250 0 07/12/2022 21250 0 07/06/2023 21250 0 07/12/2023 21250 0 07/06/2024 21250 0 07/12/2024 21250 0 07/06/2025 21250 0 07/12/2025 21250 0 07/06/2026 21250 0 07/12/2026 21250 0 07/06/2027 21250 0 07/12/2027 21250 0 07/06/2028 21250 0 07/12/2028 21250 0 07/06/2029 21250 0 07/12/2029 21250 0 07/06/2030 21250 0 07/12/2030 21250 0 07/06/2031 21250 0 07/12/2031 21250 0 07/06/2032 21250 1000000

What I am trying to achieve

ISIN	Date	Coupon	Principal
GB0004893086 CORP	07/12/2019	21250	0
GB0004893086 CORP	07/06/2020	21250	0
GB0004893086 CORP	07/12/2020	21250	0
GB0004893086 CORP	07/06/2021	21250	0
GB0004893086 CORP	07/12/2021	21250	0

Attachment 25255