Formatting data with leading zeros?

[cindy_Corwin]

Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel I have a column of data which (unformately) contains leading zeroes. I have changed the format to custom with five zeroes (00000) and made sure the column is not locked/protected BUT...Excel is NOT saving my format change so I'm losing my leading zeroes. (which is causing my import into a database to be wrong, etc., etc., etc.,etc).

I'm under the gun here as I need to update info by December 1st....I never had this problem in older version of Excel. What am I doing wrong?

 

[TheRedOak]

When I set Custom format to one zero for each of the characters I want to display, it displays fine. E.g., format cell Custom as 0000000 for cell entry of 56 yields 0000056.

Is this what you are trying to do?

 

[CyberTaz]

Is your software fully updated (12.2.3)? At one time Excel 2008 was having
some difficulty with saving formats but AFAIK it was cured by one of the
updates some time ago.

As an alternative you can also type an apostrophe (') preceding the zeroes.

HTH |:>)
Bob Jones
[MVP] Office:Mac

 

[cindy_Corwin]

Yes, that's exactly what I'm trying to do. It looks like it actually does it too but when you click in the cell, you can see there is no leading zero. If I go back and check the format, it's reading Special/Zip Code....it's almost like it's not saving my custom format?

This spreadsheet has been passed around for years and been exposed to various levels/versions of excel...this is the first time in a long time I've had problems. Maybe that has something to do with it...if I create a new spreadsheet with this format, it works fine?

I'm trying to import that data into a Filemaker database but when I should be getting 01225 as a part number, it's reading it as 1225.

Thanks for any help you can give me...it's driving me crazy!

 

[TheRedOak]

OK, one way to do this is to convert the numbers to text and add the leading zeros. Then export the text entries into the database.

For example:

In Cell A1 enter: 1566
In Cell A2 enter: =TEXT(A1,"00000&quot

This will convert A2 into a text entry that has the leading zero. Then you can export this to the database.

Does this work for you?

 

[cindy_Corwin]

Perfect!!! Thank you so much!