Add leading zeros to a column and make Excel leave them alone?

[bizzybody]

I know how to add leading zeros to a column. Select it, format cells, choose custom then enter 0# which puts a leading zero ahead of any single digit number in the column. The column contains only one or two digit numbers.

BUT when I save to CSV then re-open the file, Excel 2003 has ignored the users desires and stripped the leading zeros while opening it. They are in the CSV as saved. Grrrr! OK, I try putting leading zeros on then change the column to text. Nope! It 'helpfully' removes the zeros. (mumbled expletives) If the user commands Excel to add leading zeros the user wants leading zeros! It is not the place of the software to determine what the user needs.

I'm wanting to have the zeros in the number column, change it to text then merge with the text column to its right. I could type the numbers into the existing text column but doing that to 162 rows is not my idea of fun, and would Excel decide I still don't need those leading zeros? There are two columns of numbers, then the name.

What I need this for is to have a file with a single column of numbered names to use as a source for renaming a large number of files which have names that start with numbers, with leading zeros, that sort identically to this list. 1 to 1 replacement, should be easy for a program that can use a CSV as a data source for renaming files.

Edited March 8, 2017 by bizzybody
Because this new forum converts code blocks into un-editable images and deletes any text after that.

[jaclaz]

Are you looking for a solution/workaround or it is just a rant against Excel being (stupidly) "smart"?

I have not an Excel 2003 handy to test it, but in earlier versions if you DO NOT use .csv as extension, Excel should open an "import" wizard where you manually select whether the data field are fixed or use a separator, you can choose the separator and you can tell excel to interpret columns as text.

Is there a reason why you want the data in csv format?

jaclaz

 

[bizzybody]

A quick google search shows there should be some programs that can read from a CSV to rename files. Don't know if they can use XLS. When I doubleclick the CSV, which does have the leading zeros, Excel just open it - and removes the leading zeros.

I could try changing it to TXT and see if I can have Excel import those number columns as text.