Jump to content
Strawberry Orange Banana Lime Leaf Slate Sky Blueberry Grape Watermelon Chocolate Marble
Strawberry Orange Banana Lime Leaf Slate Sky Blueberry Grape Watermelon Chocolate Marble

MSFN is made available via donations, subscriptions and advertising revenue. The use of ad-blocking software hurts the site. Please disable ad-blocking software or set an exception for MSFN. Alternatively, register and become a site sponsor/subscriber and ads will be disabled automatically. 



bizzybody

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

Recommended Posts

Posted (edited)

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 by bizzybody
Because this new forum converts code blocks into un-editable images and deletes any text after that.

Share this post


Link to post
Share on other sites

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


 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now


  • Recently Browsing   0 members

    No registered users viewing this page.

×