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.
You don’t have to select FINISH. Just press Esc. Is silli but it works.