+ Reply to Thread
Results 1 to 18 of 18

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

  1. #1
    Registered User
    Join Date
    08-27-2012
    Location
    Roma, Italia
    MS-Off Ver
    Excel 2003
    Posts
    60

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

    Hi guys, I've found an interesting bug... wondering if you any have come up against it.

    Anyways, the issue is that when I have some cells selected then use the text-to-columns, it works fine.. but when I select an entire column, it "acts a fool"...

    I made this video to show the effect on my computer.

    What gives?

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: 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>
    DILIPandey
    +91 9810929744
    dilipandey@gmail.com

  3. #3
    Registered User
    Join Date
    08-27-2012
    Location
    Roma, Italia
    MS-Off Ver
    Excel 2003
    Posts
    60

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

    Delimited, in both cases.

  4. #4
    Forum Contributor Portuga's Avatar
    Join Date
    02-20-2004
    Location
    Portugal
    MS-Off Ver
    365
    Posts
    848

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

    Any merged cells?
    If you found the solution to your question. Mark the thread as "Solved"
    Thank everyone that helped you with a valid solution by clicking on their

    There is no such thing as a problem, only a temporary lack of a solution

  5. #5
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,095

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

    What have you set Destination to be on the 3rd step of Data>Text to columns...?
    If posting code please use code tags, see here.

  6. #6
    Registered User
    Join Date
    08-27-2012
    Location
    Roma, Italia
    MS-Off Ver
    Excel 2003
    Posts
    60

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

    Quote Originally Posted by Portuga View Post
    Any merged cells?
    Nope, none.

    Quote Originally Posted by Norie View Post
    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?
    Last edited by mexindian; 04-15-2013 at 12:24 PM.

  7. #7
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,095

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

    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.

  8. #8
    Forum Contributor Portuga's Avatar
    Join Date
    02-20-2004
    Location
    Portugal
    MS-Off Ver
    365
    Posts
    848

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

    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.

  9. #9
    Registered User
    Join Date
    08-27-2012
    Location
    Roma, Italia
    MS-Off Ver
    Excel 2003
    Posts
    60

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

    Quote Originally Posted by Norie View Post
    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.
    I never change anything on step 3... this is why I skipped it, but:

    Quote Originally Posted by Portuga View Post
    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.
    I attach a worksheet where I don't skip step 3 but get the same behaviour, and a new video >>>HERE<<< .

    A little additional information, whenever I use text-to-column with a whole column selected, one of 3 things will happen:
    1. It will work correctly
    2. It will provide a bug similar to what I have shown in these videos (shifting values up)
    3. Sometimes it shifts entries, AND doesn't delimit the last value (but this one is hard to reproduce).

    Thanks for all your hard work so far!
    Attached Files Attached Files

  10. #10
    Forum Contributor Portuga's Avatar
    Join Date
    02-20-2004
    Location
    Portugal
    MS-Off Ver
    365
    Posts
    848

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

    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.

  11. #11
    Registered User
    Join Date
    08-27-2012
    Location
    Roma, Italia
    MS-Off Ver
    Excel 2003
    Posts
    60

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

    What you say is right... but in some cases that workaround is just not feasible... at that rate it's just better to just delimit using just a selection than to add and then delete cells, no?

    Thanks for your efforts anyway.

  12. #12
    Forum Contributor Portuga's Avatar
    Join Date
    02-20-2004
    Location
    Portugal
    MS-Off Ver
    365
    Posts
    848

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

    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.

  13. #13
    Registered User
    Join Date
    08-27-2012
    Location
    Roma, Italia
    MS-Off Ver
    Excel 2003
    Posts
    60

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

    Wait... so you think this is a feature? You don't think this is a bug?

  14. #14
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,095

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

    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?

  15. #15
    Registered User
    Join Date
    08-27-2012
    Location
    Roma, Italia
    MS-Off Ver
    Excel 2003
    Posts
    60

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

    Norie, if you see the second video I posted, following each step doesn't prevent this from happening.

  16. #16
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,095

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

    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.

  17. #17
    Forum Contributor Portuga's Avatar
    Join Date
    02-20-2004
    Location
    Portugal
    MS-Off Ver
    365
    Posts
    848

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

    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.

  18. #18
    Registered User
    Join Date
    08-27-2012
    Location
    Roma, Italia
    MS-Off Ver
    Excel 2003
    Posts
    60

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

    Well written Portuga, boa ai... thanks for that. I think, at this juncture, we have to follow Norie's point and I think I will just never use columns because I can't be bothered to remember what the past setting was.

    I guess this can stay as a warning to any future readers.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1