Problem with "Convert to Text Columns Wizard"

[Maximus4587]

Hey excel cracks, My first question ever here:

I got a large dataset (100,000+ observations/rows). The single entries in my columns are separated by quotation marks (""). So far so good since I can easily separate each columns using the "Convert to Text Columns Wizard". HOWEVER, some columns are split while others are not and thus if I convert one column at a time there will be gaps in some rows. (I tried to convert multiple columns in one row at once hoping excel would cope and ... ah... nope).
Here an example (numbers are content of column; " is separater the wizard uses to distinguish 'new' columns ; | is a new column):

Before using wizard:
1"2"3"4"5
1"2"3 | 4"5

After using wizard:
1 | 2 | 3 | 4 | 5
1 | 2 | 3 | | | 4 | 5

Wanted Result:
1 | 2 | 3 | 4 | 5
1 | 2 | 3 | 4 | 5

How can I fix this automatically (dataset size forbids manual copy/paste job)? I UPLOADED an EXAMPLE file with the real data.

Btw, the original file was a CSV and i'm using a "german" excel 07. but I am accustomed to working with the english version so no problems here.

Thanks a lot for your help guys!

[Cheeky Charlie]

How about:
Insert a column at A
A1=B1&"|"&C1&"|"&D1&"|"&E1
Copy & paste values over A:A
Delete B:E
TTC on column A

Quick & effective, like a German.

[Maximus4587]

thanks bru, very simple fix. i feel stupido.

[Cheeky Charlie]

No probs, and don't, it's only easy when you know the answer.