Friday, January 15, 2010

Handle Leading Zeros in iWork Numbers

Apple iWork Numbers Leading ZerosApple's iWork '09 Numbers spreadsheet is a versatile app with a lot of power available if you open your mind and don't expect it to be Excel. It does not quite do everything Excel does, but it handles UTF-8 well (where Excel does not and has never), and I take advantage of that often. I also love the formatting options and the multiple-sheets-per-document paradigm, but that is a different post.

One challenge in both Excel and Numbers is how to handle fields with numbers with leading zeros. For instance, a part number 001234 will come out as 1234 when you import it from a CSV in either app, and lose meaning if the actual part must include the leading zeros. You can set a cell or column format in Excel as 000000, and this works the same way in Numbers, except the method's a little unfamiliar.

How to Format a Part Number Field to Preserve Leading Zeros

Here's how to not maim your part numbers.

  1. Select your column to format, and open the Cells inspector.
  2. Select Custom Format from Cell Format then click Show Format.
  3. Give the format a name, and choose the base type.
  4. Delete whatever format is in there by default and drag up an Integers type lozenge.
  5. Open the disclosure triangle, and choose "Show Zeros for Unused Digits" and you will see the #,### change to 0,000. Click Show Separator to deselect it and remove the comma. Add two digits.
  6. Click OK to save and apply the format to the selected column.

If you set the format as 000000 for a field that includes six digit numerics with leading zeros, and a mix of text with numeric part numbers, such as:

001234

P098765

005544

R-09-PCX

... the latter will not be affected by the format, which is just the right behavior we need.

I hope this tip helps someone, because not being able to set this really drove me a bit batty. Enjoy!

16 comments:

Riksa said...

Thanks! You just saved my day. I'm just learning to use Numbers and this instructions were exactly what I needed.

Rick Cogley said...

Glad it was useful to you, Riku! :-)

Josh Golackson said...

That was really helpful. I would have never figured that out. I love Numbers, but that problem was driving me crazy!

Rick Cogley said...

Hi Josh - I am glad it was helpful. Positive comments like these really make it worth the time. Thanks!

Regards,
Rick

Anonymous said...

Thanks Rick

Easy to do now that I know how. As an advanced Excel user it's frustrating not knowing the basics of Numbers in iWork. Your site has helped

Vitor Moreira said...

Is it possible to change the number of digits you have in a formula? Thanks.

Rick Cogley said...

Vitor, thanks for dropping by: can you give an example of what you mean? If you mean significant digits, Numbers is limited to, something like 15 in that area.

Regards,
Rick

Della said...

Thank you for your information! I am still very frustrated with this though, I do as you instructed and then try to fill down incrementally to fill many cells with inventory numbers (A0350 and so on) and it drops the zero every time!! I don't want to have to enter every single Inventory number, do you have a solution for that?

thanks
Della

Rick Cogley said...

Hi Della. Your problem is with the trailing zero? How is the col formatted? As text?

Anonymous said...

Thanks for this post - helped me a lot today :-)

Rick Cogley said...

Glad to have been of help.

worksinbooks said...

Finally found an answer to this problem I had trying to create address labels with zip codes starting with zero. How come this has not been addressed by programmers?

the vexed muddler said...

For some reason this is still not working for me. I have "type" set as "number and text", follow all your instructions, and when I try to drag down into the rest of the formatted column, the zeros still get removed. This is for inventory numbers with the format AA000.

Rick Cogley said...

Hi - I did not drag down to set this but rather selected the column first. I am not sure what horrors might be hiding in the method you tried...

Dan said...

It's the end of 2015 and this post is STILL super helpful. Have come across dozens of supposedly helpful work-arounds online but none have been this simple. Thank you!

Jonas said...

Great tip! But I can confirm that it only works with numbers up to 15 digits in length. Above that it starts to replace digits at the end with zeros as well. I have a massive list with 18 digit numbers that I am trying to add two leading zeroes to. It does add two zeroes, but replace the 3 last digits with zeroes (....8358735000), rendering the number useless.