Forum Discussion

Emily4242's avatar
Emily4242
Copper Contributor
May 18, 2022

Stop automatic text-to-columns formatting

This morning I was pasting text into excel and using Text-to-Columns to parse it. Later, in a different file, I was pasting other text that I did not want parsed into different columns. But excel is automatically separating it. It is messing up my file formatting and forcing me to manually recombine the text into a single column. I can't find anywhere in the options that will stop this from happening. Any suggestions? Text-to-Columns should not be an automatic function.
  • Emily4242 

    Sometimes, Excel is too clever for its own good.

    Select an empty cell.

    On the Data tab of the ribbon, click Text to Columns.

    Select Delimited, then click Next >.

    Clear the check boxes of all delimiters.

    Click Finish.

    The problem should be gone (until the next time...)

  • Emily4242 

    Sometimes, Excel is too clever for its own good.

    Select an empty cell.

    On the Data tab of the ribbon, click Text to Columns.

    Select Delimited, then click Next >.

    Clear the check boxes of all delimiters.

    Click Finish.

    The problem should be gone (until the next time...)

    • timlonggr's avatar
      timlonggr
      Copper Contributor

      HansVogelaar 

       

      Is there any way to turn it off for good? Or will I need to do this every time I've used Text to Columns?

      • HansVogelaar's avatar
        HansVogelaar
        MVP

        timlonggr 

        Excel will remember the settings during a session, but when you start Excel, there shouldn't be presets.

    • Jeff_Stubing's avatar
      Jeff_Stubing
      Copper Contributor

      HansVogelaar Thanks so much for your answer!  This has been a frustration for me for years!  FYI, it would not let me even open the Text to Columns dialog with a blank cell selected, but it worked fine with a populated cell selected.  Thanks so much!

Resources