Leading Zeroes in Excel 1

[monkeylizard]

I have a field in Excel that must be 12 characters long. If the value is less than 12 characters, it needs to zero fill on the left.

I created a Custom format of 000000000000 and it does the trick. On the worksheet, the value displays with leading zeroes. Example: 123456 displays as 000000123456. When I select the cell, the actual value (displayed in the formula bar) is still 123456.

Question:
Is there a way to actually convert 123456 into 000000123456 and not strip the leading zeroes from the Formula bar? I need Excel to actually make the value 000000123456, not just a masked 123456.




Monkeylizard
If I had only known, I would have been a locksmith. -Albert Einstein-

 

[monkeylizard]

Nevermind...Found BlueDragon2's post on creating a fixed character length in a cell.

=A1&REPT(" ",30-len(A1))

Changed this to:

=REPT("0",12-len(A1))&A1

Works as described. Thanks BD2, have a star!

Monkeylizard
If I had only known, I would have been a locksmith. -Albert Einstein-

 

[AnotherHiggins]

monkeylizard,

That will work fine, but becuase your zeros will be on the left, there is an even easier way:
[COLOR=blue white]=TEXT(A1,"000000000000")[/color]

But what you have will work fine. I'm just offering another way to skin that poor ol' cat.

[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.