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.

 

[Comfy]

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.

 

[ameya]

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.

 

[ameya]

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

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

 

[alexandru.ghita]

Hello,

You could also put ' in front of the number starting with 0 and it will not be visible in teh cell and keep your 0. instead of 012345 you should put '012345

Alex

-------------
update, sorry I see that this will not help you as you need the number formatting

 

[ameya]

also maybe copy as value if that's what you want? not sure what you're going for.

 

[BlakeCloud]

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.

I can't use them as text because i have to run vlookups through various data using these part #'s
if i vlookup while they are text it shows as #N/A until i convert them to numbers. The same applies for '

what i want is a NUMBER with a leading 0. I have multiple leading 0's that vary, meaning sometimes there are 4 leading zeros and other times there is only 1. Formulas like ="0"&A2 will not work. Is there really no way to have the numbers leave the leading 0's alone? At this point i could not even copy and paste the original numbers in anymore. I would like to change the formatted number of 00005 into a number where if i click in the cell it shows exactly that.

So essentially i need them as numbers for v lookup purposes as well as copy- paste functionality.

 

[Comfy]

I can't use them as text because i have to run vlookups through various data using these part #'s
if i vlookup while they are text it shows as #N/A until i convert them to numbers.

As I can't see your data could you try something?

Paste your serial numbers a s text and add an additional function to your vlookup.

=VLOOKUP(VALUE(Cell_Reference),Range_Reference, Column_Number, TRUE/FALSE)

 

[BlakeCloud]

okay so everyone wanted images of the data so here it is, and i will explain it in full detail.
(I have blurred out everything we are not focusing on)

Okay so in this first picture is a data compilation of all of our products (database). I am trying to do a massive update to prices, and quantities (Row X & Y)
I get the prices and quantities from the second sheet in picture two which will essentially have just the part number and price/quantity.
The problem here is that some items have the same part #'s WITHOUT their leading 0's. So lets say i'm trying to update picture one with prices using a vlookup, it will also update the items with the same part #'s. This is not the only issue that the leading 0's cause me, but it is probably the main problem. Now, converting them all back into text would be a super pain as i would have to basically redo a 10,000 item database which i've already done once this month.

I need to keep leading 0's so my data is not jumbled up and mixed together. Thanks for your help!

 

[platonic567]

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.