Forum Discussion
Solved
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 a...
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...)
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.
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.
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.