Data to be split between columns

[Jigyasa]

I have data in a single cell in the below format

1 NULL 384 JPY 0 2
2 384 555 JPY 0 10
3 555 389 JPY 0 128
4 389 1000 JPY 0 2345

I want to split the data into different columns
Like for example

C1 C2 C3 C4 C5 C6 C7 C8 C9
0 384 555 389 1000 2 10 128 2345

CAn anyone please help how to perform this activity.

 

[Rick Rothstein]

I have data in a single cell in the below format

1 NULL 384 JPY 0 2
2 384 555 JPY 0 10
3 555 389 JPY 0 128
4 389 1000 JPY 0 2345

I want to split the data into different columns
Like for example

C1 C2 C3 C4 C5 C6 C7 C8 C9
0 384 555 389 1000 2 10 128 2345

CAn anyone please help how to perform this activity.

Where did the 0 in cell C1 come from?

 

[Jigyasa]

The null value after 1 is stored as 0 in C1

 

[Rick Rothstein]

The null value after 1 is stored as 0 in C1

Okay, given that, how did the 1000 get into cell C5? Maybe you need to explain in more detail how you are choosing the numbers for outputting.

 

[Jigyasa]

I have data in a single cell in the below format


Line No Data for C1 Data for C2 Common data Raw data R1
1 NULL 384 JPY 0 2


Line No Data for C2 Data for C3 Common data Raw data R2
2 384 555 JPY 0 10


Line No Data for C3 Data for C4 Common data Raw data R3
3 555 389 JPY 0 128


Line No Data for C4 Data for C5 Common data Raw data R4
4 389 1000 JPY 0 2345




I want to split the data into different columns
Like for example


C1 C2 C3 C4 C5 R1 R2 R3 R4
0 384 555 389 1000 2 10 128 2345