Text to columns (shifting data)

[JanaD]

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.

 

[EFANYoutube]

Hi JanaD
This is a difficult one as there is no way it seems of knowing whats missing to be able to fix it
if there was a way to detect a missing field you could rewrite the cell with a double ; and it would skip a column

Excel Fan

 

[Akuini]

Hi, @JanaD Welcome to the Forum.
You need to find some patterns to determine which data should go to which column. But it will depends on what the original data looks like, whether you can find the pattern or not.
For example: how do you know text3 should go to col 6?
If let's say text3 is a date & date should go to col 6, then maybe we can write a macro to check if a field is a date then it will go to col 6.

If your data isn't sensitive material maybe you can show us about 20 rows of data as example, so we can try to find some patterns in it.