Retain Leading Zeros

**BBretschneider08** · 06-10-2009, 04:41 PM

Hello Everybody.

I am using Excel 2007

I know my way around Excel relatively well, but I can not figure this out for the life of me.

At the company I work for, I had to extract this massive list of parts (all beginning with one or more zeros) and excel stripped all of the zeros off preceeding the core of the part number.

How can I get them to come back?

This is definitely an excel issue, because DataFlo (The outdated software my company uses) shows all the zeros when the parts are exported.

I believe i just have to format the cells that the part numbers are in and we should be golden.

Somebody please give me a hand

Thanks,
Bill

**starryknight64** · 06-10-2009, 04:47 PM

Fortunately this may come fairly easy for you:

Simply select the cells you want formatted with preceeding zero's
Right-click on your selection and goto "Format Cells..."
In the "Number" tab, under "Category:" click "Custom"
Under "Type:" is a text box, in it, type in the number of 0's corresponding to the length of the largest part number
- (i.e. If the largest part number--in length--were 00001234 then type in eight 0's or 00000000)
Click OK

I hope that helps!

**shg** · 06-10-2009, 04:49 PM

Welcome to the forum, BBretschneider.

Please take a few minutes to read the forum rules, and then amend your thread title accordingly.

**BBretschneider08** · 06-10-2009, 05:18 PM

Thank you for the humble welcome!

Sadly, I already tried doing that. It appears to have changed the numbers and add zero's in front of them, but really it just looks that way. After changing the numbers, and adding zeros as you said, i clicked the formula bar, and there still aren't zeros in there. On the spreadsheet itself, it looks as if there are zero's there, but they aren't in the formula bar.

I need them in the formula bar, so i can use the VLOOKUP function.

Thanks for your help,

Bill

**emanon132501** · 06-10-2009, 05:27 PM

Try to format the cells into "Text" before pasting the data

**starryknight64** · 06-10-2009, 05:35 PM

emanon is right, try that out.

You may have to copy/paste the whole spreadsheet though. If this is a problem, let me know and I could probably cook up a quick little macro to manually add in those zero's.

**shg** · 06-10-2009, 05:37 PM

BBretschneider08,

Please change your thread title as requested.

**BBretschneider08** · 06-10-2009, 05:41 PM

I can't believe that I didn't think of that.

I am going to have to repopulate my entire spreadsheet, but I may as well do it the right way, because I really dont want to keep doing it the wrong way..

Thank you so much

Regards,
Bill