Forum Discussion
Stop automatic text-to-columns formatting
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...)
Selected an empty cell, then clicked Text to Columns ... ERROR: no data selected to parse. I really need Excel to stop auto Text to columns my CSV files as I happen to need leading zeros like it is in the csv file.
The Import from CSV file doesn't do anything different. It imports into a window where its already converted a text field with leading zeros into a number and you can't change it. The only thing you can change is if the delimiter is a comma.
Any help setting up Excel to just display the csv file in column A like it did the first time would be appreciated, thanks.
P_A_R_K_Y : Just type something into an empty cell, do the Text To Columns with a strange character like a tilde (top left corner of keyboard). THEN bring in your CSV data.
Jeff_Stubing First off, thanks for replying so quickly. Just so I'm understanding correctly. To stop excel auto formatting a csv file incorrectly. I (open a blank workbook?), then type something into an empty cell, then click the text to columns, (use delimited) and put an strange character like tilde (~ found elsewhere on keyboards outside the US), and then "bring in" your csv data. By "bring in" do you mean to select, from the data menu, "from text/CSV" and import the csv file? This doesn't work, hence why I'm putting my assumptions in brackets as I might have misunderstood your steps.
- People are making this more complicated than it is. The "blank cell" was incorrect, something must be in the cell.
On the worksheet you want to paste your data, click non-empty cell, click text-to-columns, and in the message box chose "delimited", uncheck all delimiters that are checked, and click finish. You've now told excel you want things delimited by nothing at all, and you can paste your data.
It will still take away your leading zeros.
But if you're importing data from a comma delimited file, that's a different thing. When you are importing data from a csv, you probably want text-to-columns to actually split it, check "Comma" in the delimiter list, and chose Next. choose the column in the preview that has your leading zeros and change that column from "General" to "text". Now it will parse you data to columns but keep the leading zeros intact. For that matter, look at the other columns and see if some are dates, you can specify that and the excel file will hopefully not skew them up. This screen lets you skip columns you don't use as well.- Thank you. Those steps in your first paragraph work a treat 🙂 (I don't think its us making it complicated but MS). Its unfortunate that just doubling clicking on a .csv file doesn't bring up the "Text to Columns" wizard automatically instead of presuming the column formats you gave your previous .csv is what you always want. Its a shame I have to first open a text editor, then open the .csv file and then select all and then copy, before moving to excel to do the paste and highlight and click on "Text to columns" find the column that excel always insists doesn't need its leading zeros and set to text.
Thanks again all, much appreciated. I'm going to bookmark this for when, in a years time, I have to open a .csv file. Note to microsoft: Please can you add a popup when opening a .csv file to ask if we want to use the same column format selections we chose last time we opened a .csv file or if we want to choose something different.
P_A_R_K_Y to cancel the current setup for text-to-columns, just select any cell with a text, goto text-to-columns, reach the screen with the options, unselected them all, or select the one you want, and then press ESC. That’s it.
