Text to column: Fixed width

[Shelter417]

Hello; the data I work with requires heavy use of the text-to-column function. For delimited characters, this works fine. However, whenever I select "Fixed width," Excel [usually] automatically adds at least one break line, usually towards the end of the line. Why is the program doing this? Is there any way to stop it from doing so?

[Tsjallie]

Can you upload your workbook (or part of it) so I can take a look at the code.
If necessary dummiefy the data.

[Shelter417]

Sure:

dummy workbook.xlsx

Thanks.

[Tsjallie]

Well, looked at you sheet and it's pretty obvious what's happening.
When you select fixed length, TextToColumns looks for spaces between "fields" which are common to all the rows.
You can follow this process by selecting one row and then look where TextToColumn place the breaklines.
Then select two rows and look where the breaklines are placed. You will see that all the breaklines where the two rows do not share a space will be gone.

Given that the data rows in your sheet do not have a common structure the result may show pretty random indeed.

[Shelter417]

I guess there's no way to disable the automatic break lines, then...correct? Thanks for your help.

[Tsjallie]

No, I don't think there is.
But if you feed the wizard structured data the breaklines will probably be in the right place and if not you can remove them by clicking on them or insert additional breaklines by clicking the position where you want to insert them.