Leading zeros problem

[antoine.leblanc]

Hello,

I have a list of codes (Project codes) in an Excel sheet, 5-digit long,
and most of them start with 0's : 00001, 0002, .... 00123 etc...

Now I use some kind of external application that reads into this excel
sheet, and these codes. I need them to LOOK like this, 00001, but I
also need the real actual value to be 00001.

If I type 00001 Excel converts to 1.
If I type '00001 it looks fine but the actual value is '00001,
including the ' symbol, so my application does not pick up what it
needs, expecting exactly 00001 .
And I also tried to use the Custom format "00000", there my cell looks
fine but the value still is 1.

Is there any way to get the exact value in the cell, even if it starts
with 0's ?
Thanks

 

[Pete_UK]

A formula like this:

=REPT("0",5-LEN(A1))&A1

will give you text which is 5 characters wide with leading zeroes as
required, from a number in A1.

Hope this helps.

Pete

 

[Guest]

Hi

Format the cell as text before you enter your information.

Andy.

 

[antoine.leblanc]

Yes of course, how stupid I was. Thanks A.

 

[Guest]

Thanks for the feedback. I'm sure you're not stupid really.

Andy.

 

[Beege]

Antoine

Try formatting the cells as Custom/ 00000

This will maintain the value and not turn it into text.

Beege

 

[Guest]

I'm having a similar problem - and I do convert the column to text and while
I leave the file open the leading zeroes remain. however, once I save the
file and close it out, the next time I open the file, the leading zeroes are
missing (and I also need them there).

Thanks for your help.

Rich