Link to home
Start Free TrialLog in
Avatar of rcleon
rcleonFlag for United States of America

asked on

Leading ZEros

Hi,

Back spreadsheet has a column with the employee ID the ID is a text field it should be 00123 or 01234 but it always drops the leading zero. The only way to keep the leading zero is to put and apostophy ( ' ) in front and then it works. The spreadsheet has over 10 thousand rows. So manualy is out of the question.

How can I add the leading zeros?

As always thanks for all your help.

Rafael
Avatar of diasf
diasf

Hi rcleon,

Format it as 00000. This shows the zeros up to 5 digits.

diasf
ASKER CERTIFIED SOLUTION
Avatar of mvidas
mvidas
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Rafael:

This should work for you.

- In another column enter the formula =TEXT(A1,"00000") with A1 being the cell with the ID
- Copy and paste the formula down
- Change the format of the column to Text
- Copy and Paste Special > Paste As Values for the column

I can also provide a VBA solution, but it really isn't required since this can be done with a formula.

Regards,

Jaes
Matt:

Sorry for the duplicate post.  Goes to show you that great minds think alike.  Now if I could just find my great mind I'd believe that.  ;)

Cheers,

Jaes
mvidas,

No, it would not! If you format a number with "00000", it shows 5 digits or more, like this:

123 -> 00123
1 -> 00001
123456 -> 123456

Rafael, have a try!

diasf
diasf,

I think you missed the point of it.  I did say yours was the easiest, it really just depends on what Rafael is looking to accomplish (exporting or displaying).  If you put "1234" in a cell and format the cell to "00000", it will show 01234, but if you look at the cell's value in the formula bar it will show just 1234 (the formatting only affects how it is displayed).

Using the formula allows it to retain the 01234 in the cell, really only necessary for specific reasons.

Matt
Jaes - Great minds do think alike.  So do ours.
Matt,

Yes, you're right. My solution shows the leading zeros, but in fact they are not there. Just showing up. Your solution makes them permanent.

diasf
Most of the time it would not matter, and in a lot of cases the formatting is preferred (like making sure numbers show 2 decimals while retaining the numerical value).  As long as Rafael doesn't need to retain the text of the leading zeros, your formatting way would be the best (easiest at least)
Avatar of rcleon

ASKER

You guys are all awesome. Every time I post a question I get multiple great answers.

Also I do think the great minds think alike. You guys all have great mind.

Thanks

Rafael