How do I stop auto Text To Column

[jayemoh77]

EXCEL 2016

I downloaded a file as a .csv from a work database. I'm opening the file in excel and it is automatically splitting the first column (last name,first name) into 2 columns. I'm assuming it's because it thinks it's helping me because it sees the comma. I do not want this to happen. How can I disable this. I want to split my data into columns my self.

Thanks!
Julie

 

[Rick Rothstein]

EXCEL 2016

I downloaded a file as a .csv from a work database. I'm opening the file in excel and it is automatically splitting the first column (last name,first name) into 2 columns. I'm assuming it's because it thinks it's helping me because it sees the comma. I do not want this to happen. How can I disable this. I want to split my data into columns my self.

I don't have XL2016, but this should work... type something in a cell and with that cell selected, call up the Text To Columns dialog box, select delimited on the first dialog page and uncheck all checkboxes on the second dialog page, then click the Finish button. The problem is that the dialog box remembers the last settings (whether set manually or by code) and then uses them when pasting data, so the procedure I outlined makes it remember "nothing" so it will apply "nothing" to your pastes until the next time you set it to something else.

 

[jayemoh77]

Thanks Rick. It didn't work.

Clarification: I'm opening a downloaded file. I'm not opening a blank excel document and adding data to it.

I also read somewhere that you can hold down the shift key when opening the file and it will disable to the text to column wizard. That ALSO does not work.

 

[Joe4]

How exactly are you opening this file in Excel? When you try opening CSV files directly in Excel, it will automatically delimit it based on commas.

You can override that automatic behavior by doing one of the following:
1. Opening the file from the Get External Data ribbon on the File menu (choosing the "From Text" option)
- or -
2. Change the extension from ".CSV" to ".TXT". Then open it.

Either option will cause the Data Import Wizard to fire, where you can choose the import features.

 

[jayemoh77]

Thank you Joe4

For 15 years I did this:
Download a file from an external source and save as .csv.
I go to my downloads file and open it, click text to column. done

I upgraded to office 2016 this morning. I tried what you said above. It works but now i'm required to do a lot more steps. thanks much!

 

[Joe4]

I go to my downloads file and open it, click text to column. done

How exactly did you do this?
Do you mean go to Windows Explorer, go to the Download Files folder, and open it from there?
And it would open it in Excel, and dump everything in one column and you would use Text to Columns on it?
I have been using Excel for over 20 years, and I have always had the experience that if you open a CSV file directly in Excel, it automatically delimits it for you.
Which version of Excel were you using before?

It works but now i'm required to do a lot more steps

If you choose option 1, it really should be almost the same number of steps. You are just opening the file from Excel instead of from the Downloads folder.

By the way, you may be able to create a simple macro to do it for you, where you just go into Excel, and run a macro that pulls up a file browser where you pick the file, and it does the rest for you (all your Text to Columns settings). So it may be even faster than what you used to do.

 

[jayemoh77]

used to have excel 2006. The file is downloaded in to a folder on my computer. I just go to the folder and open the file and everything used to be in one column.

 

[Joe4]

Are you using Mac?
There was no Excel 2006 for Windows (see: https://en.wikipedia.org/wiki/Microsoft_Excel)

 

[jayemoh77]

sorry! 2010. Using a PC.