Text to columns fixed width errors

Question

Whenever I try to use the Text to Columns with Fixed Width it seems to almost randomly choose the width in characters it splits on rather than what I asked for. It also isn't even consistent. Here are some screenshots.

Here is what I request:

Here is what I get:

It isn't even consistently separating the columns on top of it doing incorrectly width wise. I recorded a video of this if you would like to see it in action. (Sorry about the volleyball, it was only supposed to be there during my account info but it showed up whole time). Also the source file is available here.

Answer 1

Hi Brian,

Based on your process, the reason Excel didn’t convert correctly is that the original data’s width is not unified.

Assuming you want to split the Column into |Date| Time, instead of using fixed width, you could separate columns with space.

These are the steps to do that:  

>Select the column >Text to Column >Choose Delimited >Next

>Select Space >Next

>You could set your first column format into Date. >Finish

This is the outcome:

For more information, you could refer to this article: Split text into different columns with the Convert Text to Columns Wizard

If that’s not what you meant, please clarify what columns you want, and let us support you better.

Regards,

Alex

Answer 2

I wasn't splitting it on delimiters I was splitting it based on number of characters wide. I was only using dates as example. I tell it to split based on 9 characters wide for example, I don't care what the characters are. Try to do what you did using fixed width rather than delimiters.

Answer 3

Hi Brian

Sorry I misunderstand.

The reason you get the width errors, is the number in the ruler divided by Break Line in the Data Preview means the number of characters. in your original post, the first column has 9 characters, the first line performs right cause space count one.

But the date, however, they have format behind them, that why it performs strange.

To see the difference, you could try to convert a column of pure letters.

Regards,

Alex

Answer 4

Hi Brian,

Do you have other concerns about Text to Columns? Welcome to discuss with us.

Regards,

Alex