Text-to-Columns doesn't work well when column is selected

Okay.. though I did not watched the video due to system restriction but wanted to know if you used the option "Delimited" or "Fixed width" while performing "Text - to - column" ?


Regards,
DILIPandey
<click on below * if this helps>

Any merged cells?

What have you set Destination to be on the 3rd step of Data>Text to columns...?

Originally Posted by Portuga

Any merged cells?

Nope, none.

Originally Posted by Norie

What have you set Destination to be on the 3rd step of Data>Text to columns...?

I think now we're getting somewhere. Even though I never changed that range, When I select the column, the reference changes to the first row in that column... so probably it starts dumping everything there... that does explain my behaviour.

The question is WHY? That can't possibly be the way the tool is intended to work, is it?

In the video you are just pressing Finish after the 2nd step instead of stepping through the wizard.

That isn't really how the tool is meant to be used, especially if you've changed one of the parameters, eg the range.

When you select the column, it sould change to the first row since you are selecting the first row also.

If I simulate both your actions on my sheet, i get them to work properly. Something else is causing it

Without looking at your sheet, it will be hard to see what it is.

Excel seems to remember the previous settings of the "Text to columns" and applies it to future uses.

Since you select the data starting in B4 first and on the second time you use the whole column, somehow the function is applying those settings to the column selection. Since the first cells are empty, it will create this behavior.

If you populate cells B1:B3 with data, any data, and apply the Text to columns you won’t see the error anymore.
And if after that you delete B1:B3 and apply the feature again, the problem will not happen.

Selection for sure, If you use the workaround once in 1 column, it should work the same way afterwards for all column selections though.

This is an explanation of why the feature does what it does.

This is a feature/bug/whatever that also applies to other things in Excel, for example Find.

Why not follow each step, making sure everything is set as it should be, instead of clicking Finish after Step 2?

Can I ask why you are selecting the entire column?

Do you really want to do the text to columns on every single row?

PS Can't watch the video right now.

This is the feature:
Excel seems to remember the previous settings of the "Text to columns" and applies it to future uses.

It is a useful feature as this means that you don’t always have to choose your delimiter for example when performing a task.
If you always use the "-" as delimiter, excel will remember that and will use it for future uses, instead of always having to choose your delimiter every time you use this.

Same way, if you always select the column, excel will apply those settings the next time you use it, and the problem you showed in your videos will not happen.
You can even skip steps 2 and 3 and it will always perform the same.

Now, if you select a range and do it, and then select a column with blank cells on the top (this problem will not happen if you have populated cells on the top), this issue will happen since excel is remembering the settings for the range and applying it to the column but since the column has blank cells, you got this issue.

Once you understand this, you will be able to avoid trouble.