Keep my leading 0's

[BlakeCloud]

Hi,

I work with a lot of data with part numbers. Let's assume that the cells are formatted by Custom - 000000. I have 3 numbers:
001534
095432
473827

Normally without the formatting my numbers would look like:
1534
95432
473827

I copy and pasted the actual 0's and excel seems to think that since they are numbers i don't want the 0's that i copy and pasted (excel 2007). Formatting works.... visually, but i need a way to be able to copy and paste those 0's a second time. Meaning when i click on a cell it should show 001534 in the formula bar. I CAN'T have these numbers as text, Zip code formatting doesn't work. If you can solve my problem i would be beyond grateful, i'm sorry if this has been asked before i can't find it anywhere.

*Rant*
whoever thought deleting something i specifically copied into my cells was a good idea needs a reality check.

 

[BlakeCloud]

So far, I have seen only 1 line with leading 0's in your sample i.e. A12 of 2nd sheet containing "049258#$". I don't see how cell A12 can be recognize as a number by Excel.

I think it will better if you post some sample data as text (just the ones where you are facing problem) and the desired results.

that's what i'm saying. i WANT the leading 0's to appear, but they aren't. I posted those pictures simply as examples of what the numbers look like. So row A12 is a discontinued part which is why there is a #$. As you can tell i have a highlight condition on in row C of the first picture. One of the numbers is highlighted red because its a 'duplicate value', but if it had its leading 0's it would not be. I hope that clarifies things.

 

[platonic567]

Then formatting as text would be the way to go for keeping the leading 0s

 

[BlakeCloud]

Then formatting as text would be the way to go for keeping the leading 0s

Yeah i'm aware that formatting as text keeps the 0's but i need them to be numbers when i cross check price and quantities throughout 5 different spreadsheets. Is there no other way to keep the 0's AND have them be numbers?

I can't do text because the vlookup will come up as #N/A
I can't do custom formatting because it doesnt actually keep the 0's

 

[BlakeCloud]

Explanation attempt #2

ROW A: Manufacture Part #
ROW B: OUR PART #
ROW C: Supplier PART #

Notice how one of the products listed has a leading 0 in our part #. This means that the manufacture and vendor part #'s should as well. I can't use a v lookup function without getting the 100+ parts jumbled up by them having the same part #'s because excel thinks it's funny to remove the leading 0's. If there was a 0 in front of the part # with the leading 0 in question then life would be peachy for me. I CAN NOT format this text by anything but number or the vlookups will not work, as well i can not magically change the part #'s given to me. Thanks again for looking at all this!

 

[BlakeCloud]

Unfortunately, this will always return text if you add 00s.
One "solution" (this will make the numbers into text) is to use a formula like
="00" & A1
where A1 is where the part numbers are located.

Could you provide more info about why you need the leading zeros.

More specifically, why can you not format them as text or input them with an apostrophe?

That will allow us to suggest other alternatives.

sorry, something like this if you choose to do it my way

=REPT(0,6-len(A1)) & A1

I tried all the methods listed in the post so far, and none seem to work. I guess what i'm asking for is beyond excel's capacity, and may just be a simple oversight in the program's developement. What i'm essentially looking for is a way to 'turn off' erasing leading zero's.

 

[platonic567]

Yeah i'm aware that formatting as text keeps the 0's but i need them to be numbers when i cross check price and quantities throughout 5 different spreadsheets. Is there no other way to keep the 0's AND have them be numbers?

I can't do text because the vlookup will come up as #N/A
I can't do custom formatting because it doesnt actually keep the 0's

Ok so this seems to be a vlookup problem then. Give a small sample data where you are facing this vlookup problem when you format part # column as text thus keeping the leading 0s.