How to remove unknown whitespaces when splitting Excel data to multiple columns.

[NiranjanK1]

Hi All,

I have a excel data with unknown white spaces,

for one field split i have 1,

for second fields split i have 5 spaces,

for 3 field split i have 11 spaces,

how can i handle to create below image output.how to remove unknown whitespaces when splitting Excel data to multiple columns.

Thanks & Regards,

Niranjan

[binuacs]

@NiranjanK1 Can you upload the sample input file ?

[NiranjanK1]

@binuacs Here is the sample data

Instrument          Date          Cancelled        M Date        IIN                    Amount           C Rate           Payment Frequency

Active                     2016/04/04                                         2024/12/02   N000290550                       240000000                    12.2

N000290550

N000290550                                                 12.233

N000290550                                                 12.317

N000290550                                                 12.358

[binuacs]

@NiranjanK1 One way of doing this

[NiranjanK1]

@binuacs Thanks for your Solution. This is sample data but i have to follow same approch for mutiple fields. 

Do i need to know any other techniquies or shall i follow/use the same approch. please suggest.

[binuacs]

@NiranjanK1 since there are no patterns in your input file it is very difficult to parse in a dynamic way. Another option I am thinking look at the field pattern for eg: IIN field value always starts with N followed by digits , C. Rate field has the pattern 12.12 etc then parse.

[NiranjanK1]

@binuacs Can you give me input file or please have data in text input file. Not sure from where can i see F1 field. 

[NiranjanK1]

@binuacs In original data except IIN, All other fields have different data(unique data). 

[binuacs]

@NiranjanK1 I used the option in the input tool, that's why you are seeing the F1 column (default column assigned by Alteryx). .Attaching the input file

[binuacs · Accepted solution]

@NiranjanK1 your actual input is in a text file or excel file? If it is in a text file can you attach a sample file with more data? will find some patterns and try to parse the fields based on that