Text to columns (shifting data)

JanaD

New Member
Joined
Jan 7, 2021
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

EFANYoutube

Board Regular
Joined
May 19, 2017
Messages
162
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

Well-known Member
Joined
Feb 1, 2016
Messages
2,962
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,861
Messages
5,627,314
Members
416,239
Latest member
Counselor85027

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top