How to split column into multiple column based on the length of the cell

Question

Hi Team,

I have an input with column A having different set of values in each row and the length of these cells are not certain means the length of each cell may vary from 6 to 300.

My concern is I want to split this into multiple column with length in each new column not exceeding 66.

I have attached the sample input and required output.

Please help me on this.

Sample Input & required Output.xlsx

philipmannering · Accepted Answer

Hi @Gsiva3

I think this does what you need.

AmalinaH · Accepted Answer

Hi @Gsiva3 you could use a RegEx tool to achieve this. The RegEx tool can specify the length of the string you would like to parse to. In this case we set the maximum length to 66.

I set the number of columns as 5 columns since the maximum character length is 300 (therefore there can be maximum of 5 columns to split to given each column is 66 characters long).

Please check if this matches your desired output (as there are some discrepancies with your required output columns).

Qiu · Accepted Answer

@PhilipMannering 
Nice one.

I was almost going to macro. 😁

grazitti_sapna · Accepted Answer

@Gsiva3, in case you want this workflow to be dynamic for n number of columns, made a few tweaks to @PhilipMannering instead of tokenizing into columns I tokenized the data into rows.

I hope this helps!

Thanks!