29.6.11

Split cell content into multiple column, non fixed field length

I recently get a file to work on generated by crystal report and I had to deal with this format as no other were available. In my case, data were supposed to be split into 11 columns, in the original file there were all in 1, data were separated by a variable number of space. This post will present a process to split cell content when you have no markup. JSON code is provided for reference below.


1/ value.trim() or edit cell => Common transform => trim leading and trailing space

2/ value.partition(" ", true).join("|") please note the double space
if you are repeating this operation several time, use the History tab and click Reuse so you don't have to key again the expression.



3/ Split into several column based on | and max 2 column




4/ Rename the column
5/ Repeat this process for the new column


code: please note that column named is col1, is you are renamed as the JSON code has been generated on a different data set.