Forum Discussion
Re: Stop automatic text-to-columns formatting
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.
5 Replies
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.
