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...)
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.
