Hi all,
I have a huge amount of data that come from customers who are supposed to fill different fields in online form where all fields are not mandatory, therefore somebody fills them in, somebody don't (I can't change this).
What I then get is all data in one cell separated by ; which would be easy to put to the columns but because there are no empty fields accounted the data shift. Here is an example:
1. row: Monday;John Smith;English;text1;text2;text3
2. row: Monday;John Smith;English;text1;text3
So when I sort this text to the columns, not all text is in the right column as from above example I get:
Monday John Smith English text1 text2 text3
Monday John Smith English text2 text3
Text 3 needs to go to 6th columns rather then 5th. With the amount of data I have and never knowing how many empty fields there are supposed to be it is causing me a huge problem.
Could you advise on any tricks for this?
Thank you.
I have a huge amount of data that come from customers who are supposed to fill different fields in online form where all fields are not mandatory, therefore somebody fills them in, somebody don't (I can't change this).
What I then get is all data in one cell separated by ; which would be easy to put to the columns but because there are no empty fields accounted the data shift. Here is an example:
1. row: Monday;John Smith;English;text1;text2;text3
2. row: Monday;John Smith;English;text1;text3
So when I sort this text to the columns, not all text is in the right column as from above example I get:
Monday John Smith English text1 text2 text3
Monday John Smith English text2 text3
Text 3 needs to go to 6th columns rather then 5th. With the amount of data I have and never knowing how many empty fields there are supposed to be it is causing me a huge problem.
Could you advise on any tricks for this?
Thank you.